Flat file to hierarchy target mapping

[mohammadaamir_khan]

Hi,

I am using PI 7.4 and using graphical mapping.

My source is a flat file which I have map it to a hierarchical output based on line number value.

Basically DocumentLineNumber can be repeated based on LotNumber values.

INPUT

<row>
<DocumentNumber>11</DocumentNumber>
<DocumentLineNumber>10</DocumentLineNumber>
<ItemNumber>MAT-123</ItemNumber>
<LotNumber>lot1</LotNumber>
<TransactionQuantity>1</TransactionQuantity>
</row>
<row>
<DocumentNumber>11</DocumentNumber>
<DocumentLineNumber>10</DocumentLineNumber>
<ItemNumber>MAT-123</ItemNumber>
<LotNumber>lot2</LotNumber>
<TransactionQuantity>2</TransactionQuantity>
</row>
<row>
<DocumentNumber>11</DocumentNumber>
<DocumentLineNumber>20</DocumentLineNumber>
<ItemNumber>MAT-343</ItemNumber>
<LotNumber>lot3</LotNumber>
<TransactionQuantity>5</TransactionQuantity>
</row>
<row>
<DocumentNumber>11</DocumentNumber>
<DocumentLineNumber>30</DocumentLineNumber>
<ItemNumber>MAT-742</ItemNumber>
<LotNumber>lot4</LotNumber>
<TransactionQuantity>4</TransactionQuantity>
</row>

Desired Output

 <Request>

      <DocumentNumber>11</DocumentNumber>

      <lines>        

         <DocumentLineNumber>10</DocumentLineNumber>

         <ItemNumber>MAT-123</ItemNumber>

         <lotItemLots>

            <LotNumber>lot1</LotNumber>

    <TransactionQuantity>1</TransactionQuantity>

         </lotItemLots>

         <lotItemLots>

            <LotNumber>lot2</LotNumber>

    <TransactionQuantity>2</TransactionQuantity>

         </lotItemLots>

       </lines>

       <lines>        

         <DocumentLineNumber>20</DocumentLineNumber>

         <ItemNumber>MAT-343</ItemNumber>

         <lotItemLots>

            <LotNumber>lot3</LotNumber>

    <TransactionQuantity>5</TransactionQuantity>

         </lotItemLots>

       </lines>

       <lines>        

         <DocumentLineNumber>30</DocumentLineNumber>

         <ItemNumber>MAT-742</ItemNumber>

         <lotItemLots>

            <LotNumber>lot4</LotNumber>

            <TransactionQuantity>4</TransactionQuantity>

         </lotItemLots>

       </lines>

   </Request>

How do we convert this in graphical mapping?

Thanks

[Ryan-Crosby]

Using a combination of techniques for sorting and split by value on value change, but what would need to be sorted depends on whether the source can have multiple document numbers or only one.

[mohammadaamir_khan]

DocumentNumber is unique in the paylaod.

[Ryan-Crosby · Accepted solution]

Here are a bunch of screen shots showing the individual target fields with a combination of sorting, collapsing contexts and splitting by values (some by each value and others by value change, which I would leave to you to figure out), and one use of formatByExample, along with a screenshot of the result in CPI (I don't have PI/PO anymore).

Regards,

Ryan Crosby