SAP PI 7.4 IDoc to Flat File - Group Flat File Output

[Former Member]

Hi Everyone,

I've an issue with my IDoc to Flat File scenario. I would appreciate any advice you could give on this.

I have mapped IDoc SHPORD|DELVRY06 to produce a fixed length flat file output. The IDoc's are collected in SAP and sent out to PI. We are appending all the IDocs sent out to one flat file.

For every IDoc line segment E1EDL24 we need one line in the flat file output. This works fine.  (E1EDL24 ->removeContexts>Record)

The problem I have is grouping the lines in the flat file. Currently the output comes out like:-

<Transaction><Key><Material> <Quantity>

AB809MAT1 20

AB813PRD1 15

AB809MAT1 20

AB814PRD2 12

AB813PRD1 15

AB999ITM1  10

AB809MAT1 25

But the requirement is to have the output group (rolled up) by <Transaction><Key><Material> <Quantity> as follows:-

AB809MAT1 65

AB813PRD1 30

AB814PRD2 12

AB999ITM1  10

Do i need to perform another mapping step ?  Or is there I way I can achieve this within the existing mapping ? I've searched around but for some reason can't seem to find this scenario. Any help would be greatly appreciated.

Thanks for your time.

JE

[Former Member · Accepted solution]

Hi Sritharan,

Thanks for your time.

Still not working. I'll describe a bit further. I've mapped the IDoc fine - so now I'm going to use another message mapping to format the flat file output.

The Message type looks like this:

<Record>

--<Transaction>

--<Key>

--<Material>

--<Quantity>

In order to group the output my mapping looks like this.  I've concatenated Transaction,Key and Material to give me a KeyValue since we want a new record when this changes.

To sum the quantity for every record I need to formatByExample when mapping Quantity right ?

<Record>KeyValue-> removeContexts->sort->SplitValue(ValueChange)->collapseContexts-> <Record>

<Transaction>                                       ---->      <Transaction>

<Key>                                                      --->       <Key>

<Material>                                               ---->     <Material>

<Quantity>

    Quantity->removeContexts-> formatbyExample->UDF(Add)-><Quantity>

KeyValue-> removeContexts->sort->SplitValue(ValueChange) ->                                                      

This doesn't work. The problem is relating the KeyValue and the Quantity - the Quantity doesnt stay with the Keyvalue.

I've attached screenshots below. Input show's 4 records and Output Shows 3 records which correct since we want to group where the KeyValue is the same (duplicated)  however the Quantities are not in the right context.

My output should be:

AB

813

PRD1

15

AB

809

MAT1

45

AB

999

ITM1

10

Thanks for your support.

JE

[Former Member · Accepted solution]

Hi all,

I've progressed a small bit on this. I know now my problem is a matter of sorting.

Based on the Key I used to group the message I need to then sort the rest of the fields using this Key. I'm investigating how to do this.

Thanks

JE

[Former Member · Accepted solution]

you can check the  blog

[former_member186851 · Accepted solution]

Hello Joel,

Why don't you try sort by key function.?

[Former Member · Accepted solution]

Thanks,

I think I will need a UDF (although I don't know much about them) to acheive this. I can't see a way to do it graphically.

[Former Member · Accepted solution]

Thanks Sritharan,

I have tried with sortByKey but i cannot see a way to make it work.

The problem is with the other fields in the Record - i can't link them to the KeyValue (which I'm using to create one idoc per KeyValue so duplicates are not created)

So linking AB809MAT1 to the following:-

AB

809

MAT1

20

etc.

isn't looking possible for me at this stage. I will try looking into UDF maybe.

Thanks for your help

[Former Member]

Hi all,

I've resolved this issue. I was not using the sort by key function correctly - Once I resolved the mapping and used the UDF (to add values in a context) from Sritharan my solution is now working perfectly. The solution is pretty straight forward once I got my thinking correct.

I would like to thank everyone for taking the time to help me here; particularly Sritharan for his effort in doing his utmost to help out.

So based on the above and for the record the correct mapping is as follows:

1. First I create a normal mapping from IDoc to Message Type MT_FILE (Mapping 1)
2. I then created another mapping to group Step 1 with the input (coming from Step 1) and the output being the same Message Type
3. SortKey = Concat <Transaction> + <Key> + <Material>
4. Record node mapping to group duplicate values together into one record

1. Sub Nodes (all fields under Record are mapped the same - here my example for Qty)

Thanks

JE

[former_member186851]

Nice to Hear Joel.

[former_member186851]

Hello Joel,

Requirment is little bit confusing.

You wish to collect the line items for a particular idoc.?

[Former Member]

Hi Sritharan,

Thanks for replying. No I don't want to collect for a particular IDoc.

My scenario is fine but the flat file lines need to be grouped as described. So instead of having numerous line repeating with the same key fields they should be grouped in the flat file.

Thanks

JE

[former_member186851]

Hello Joel,

Try the logic below

Keyfield---->SortFunction--->split by value(valuechange)------>Collapsecontexts..

[Former Member]

Hi Sritharan,

Thanks.

This will give me the context I think but i think I need something more to sum the quantities ?

So

AB809MAT1 20

AB809MAT1 20

AB809MAT1 25

will be

AB809MAT1 65

Thanks

Gary

[former_member186851]

Hello Joel,

Just use this small UDF afte the context change to add the values of context

Code :

int out =0;

for(int i=0;i<var1.length;i++)

    {

     out = out+var1[i];

     }

result.addValue(out);

[Former Member]

Thanks Sritharan,

That's excellent - it's almost there.

My last issue is with the context changes

Keyfield---->SortFunction--->split by value(valuechange)------>Collapsecontexts..

I have mapped this to the Record node under which my fields are.

It's not grouping exactly right. So for this example i'm testing the output in the message mapping test tab.

AB813PRD1 15

AB809MAT1 20

AB814PRD2 12

AB999ITM1  10

AB809MAT1 25

The resulting output is :

AB809MAT1 45

AB809MAT1 25

AB813PRD1 15

AB814PRD2 12

I would expect :

AB809MAT1 45

AB813PRD1 15

AB814PRD2 12

AB999ITM1  10

It appear to be pushing the contexts out by one. So instead of showing AB809MAT1 45 just once in the output it's showing twice - the first one has the total value while the next one is the original value.

When I display queue on CollapseContexts the sorting etc. looks fine - just the resulting output doesn't look like this.

I think something small is off here.

Thanks very much for your help on this.

JE

[former_member186851]

Hello Joel,

Try chaning the root context of the key.

And where your using the UDF after collapse context right?