Skip to main content Skip to complementary content
Qlik Resources
Announcements
Loading SearchUnify's search If you need assistance with your product, please contact Qlik Support.
Qlik Customer Portal
Loading SearchUnify's search If you need assistance with your product, please contact Qlik Support.
Qlik Customer Portal

Configuring the mapping

Use functions to define the transformations to perform on your structures. The following examples illustrate a few complex mapping patterns.

Combining nested loops into a simple loop

Convert an XML structure with nested loops into a flat CSV structure by combining nested loops.

Before you begin

You have created a map and added an input and an output structure.

About this task

In this example, the input XML file contains information about a purchase order. Each Address element contains an Items element, which contains at least one Item element. The goal of the transformation is to have a CSV file with a row for each Item. Each row should also contain the relevant shipping information.

The example uses the following XML sample as input:
<PurchaseOrderShipping PurchaseOrderNumber="99503" OrderDate="2011-03-15T12:10:03+05:30">
                  <ShipDate>2011-04-30T23:50:00+05:30</ShipDate>
                  <Address>
                  <Name>Deandre King</Name>
                  <Street>4894  Winding Way</Street>
                  <City>Southfield</City>
                  <State>MI</State>
                  <Zip>48075</Zip>
                  <Country>USA</Country>
                  <DeliveryNotes>Please leave packages in shed by driveway.</DeliveryNotes>
                  <Items>
                  <Item PartNumber="872-AA">
                  <ProductName>Lawnmower</ProductName>
                  <Quantity>1</Quantity>
                  <USPrice>148.95</USPrice>
                  <Comment>Confirm this is electric</Comment>
                  </Item>
                  <Item PartNumber="926-AA">
                  <ProductName>Baby Monitor</ProductName>
                  <Quantity>2</Quantity>
                  <USPrice>39.98</USPrice>
                  </Item>
                  </Items>
                  </Address>
                  <Address>
                  <Name>Burl Clark</Name>
                  <Street>3807 Pointe Lane</Street>
                  <City>Fort Lauderdale</City>
                  <State>FL</State>
                  <Zip>33308</Zip>
                  <Country>USA</Country>
                  <Items>
                  <Item PartNumber="356-KX">
                  <ProductName>Gas canister</ProductName>
                  <Quantity>1</Quantity>
                  <USPrice>123.02</USPrice>
                  </Item>
                  </Items>
                  </Address>
                  </PurchaseOrderShipping>
The output structure is defined as follows:
ItemsShipping
                  item (0:*)
                  PurchaseOrderNumber
                  ShipTo
                  ShipDate
                  PartNumber
                  ProductName
                  Quantity
                  USPrice

Procedure

  1. Place the input PurchaseOrderNumber and ShipDate on the corresponding output elements.
  2. Open the Functions tab and drag the Concat function on the output ShipTo element.
    This function will be used to concatenate the different address elements from the input into a single output element.
  3. Place the following input elements on the Concat function in the Value tab of the ShipTo output element:
    • Name
    • Street
    • City
    • State
    • Zip
    • Country
    Information noteTip: You can place an element on Concat to add it at the top of the list, or you can place it before or after another element.
    Placement of input elements.
  4. Double-click the Concat function to define the string to add between the concatenated elements.
    This parameter is empty by default. If you want the different parts of the address to be separated by a space for example, enter a space in the Join String field.
  5. Place the input PartNumber element to the corresponding output.
    Since the output structure is already looping on the input Address element, this causes a conflict.
  6. In the dialog box that opens, select Change the output map element looping and click OK.

    When selecting this option, the output structure will loop on the input Item element. A context is automatically added to the output item loop.

    Context added to output item loop.

    As a result, if an Address element has multiple Item elements, the output will contain a row for each Item.

  7. Place the input ProductName, Quantity and USPrice elements to the corresponding output elements.

Results

Your mapping is configured. If you have a sample document, you can click Test Run to see the result. In this example, it looks like this: 
PurchaseOrderNumber,ShipTo,ShipDate,PartNumber,ProductName,Quantity,USPrice
               99503,Deandre King 4894  Winding Way Southfield MI 48075 USA,2011-04-30T23:50:00+05:30,872-AA,Lawnmower,1,148.95
               99503,Deandre King 4894  Winding Way Southfield MI 48075 USA,2011-04-30T23:50:00+05:30,926-AA,Baby Monitor,2,39.98
               99503,Burl Clark 3807 Pointe Lane Fort Lauderdale FL 33308 USA,2011-04-30T23:50:00+05:30,356-KX,Gas canister,1,123.02

Aggregating nested loops into a simple loop

Convert an XML structure with nested loops into a flat CSV structure by aggregating nested loops.

Before you begin

You have created a map and added an input and an output structure.

About this task

This example uses the same input structure as in Combining nested loops into a simple loop. The goal of the transformation is to have a CSV file with a row for each Address element containing information about the delivery and the products.

The output structure contains multiple PartNumber and ProductName elements so that each row can contain all the products to be delivered to a specific address. It is defined as follows:
AddressesShipping
                  Address (0:*)
                  Name
                  Street
                  City
                  State
                  Zip
                  Country
                  DeliveryNotes
                  PartNumber_1
                  ProductName_1
                  PartNumber_2
                  ProductName_2
                  PartNumber_3
                  ProductName_3

Procedure

  1. Place the following input elements to the corresponding output elements:
    • Name
    • Street
    • City
    • State
    • Zip
    • Country
    • DeliveryNotes
  2. Place the input PartNumber element to the output PartNumber_1.
    Since the output structure is already looping on the input Address element, this causes a conflict.
  3. In the dialog box that opens, select Generate AgConcat value expression and click OK.
    With this option, all values for the PartNumber elements contained in each Address element are aggregated in a single field using an AgConcat function. If you want to have a single value per field, you need to use a filter.
    Use of AgConcat function.
  4. Place a SingleIndex function to the Filter argument in the Value tab of the output PartNumber_1 element.
    To get the value of the first PartNumber element, you can leave the default value for the index.
  5. Place the input PartNumber element to the output PartNumber_2 and PartNumber_3 elements, then select Generate AgConcat value expression in the dialog boxes that open and click OK.
    The filters are automatically configured based on the expression defined in PartNumber_1.
    Mapping of input PartNumber element to output elements.
  6. Repeat the previous steps to map the input ProductName element to the output ProductName_1, ProductName_2 and ProductName_3 elements.

    Example

    Mapping of input ProductName element to output elements.

Results

Your mapping is configured. If you have a sample document, you can click Test Run to see the result. In this example, it looks like this: 
Name,Street,City,State,Zip,Country,DeliveryNotes,PartNumber_1,ProductName_1,PartNumber_2,ProductName_2,PartNumber_3,ProductName_3
               Deandre King,4894  Winding Way,Southfield,MI,48075,USA,Please leave packages in shed by driveway.,872-AA,Lawnmower,926-AA,Baby Monitor,,
               Burl Clark,3807 Pointe Lane,Fort Lauderdale,FL,33308,USA,,356-KX,Gas canister,,,,

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here