Mapping Challenge!

[Former Member]

Dear Java Gurus!

I have ethe following file message that I bring to XI in XML.

H|A|01|10|00000002|1

H|A|01|10|00000002|2

H|A|01|41|00000002|1

H|A|01|41|00000002|3

H|A|02|41|00000002|1

H|A|01|10|00000025|2

H|A|01|10|00000025|1

H|A|02|10|00000025|2

The field names are: Field1, Filed2, DC, WH, Store,Qty.

I need to consolidate this structure based on DC and then WH and then Store and then sum the Qty. The result should look like:

H|A|01|10|00000002|3

H|A|01|41|00000002|4

H|A|02|41|00000002|1

H|A|01|10|00000025|3

H|A|02|10|00000025|2

Actually the number of result nodes created should be based on the above logic and the field Qty comes inside the result nodes. So the field Qty needs a mapping too.

I could consolidate at one level for example, Store using the following code:

MappingTrace trace = container.getTrace();

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

{

if(i==0 || (!a<i>.equalsIgnoreCase(a[i-1])))

{

result.addValue(a<i>);

}

}

But if I do a nested "for" then the mapping of the field "Qty' does not work! May be my nested for has problems. Any one has done anything like this?

Please help.

[Former Member · Accepted solution]

Hi Param,

first SORT the entries.

In the for-loop ask for the changes.

If there is no change --> inkrement a count

If there is a change --> add th counter to result and reset the counter

Regards Mario

[Former Member]

Hi,

By consolidation, you mean that only for the lines that have identical DC, WH as well as Store, you have to add quantity. Correct me if I am wrong.

You could have following approach to this problem.

Create a UDF passing following parameter. It should be passed as a context or queue, depending on requirement.

strInput = Concatenated string of DC, WH, Store and Quantity.

So, we should have following array as input:

0110000000021

0110000000022

...

Sort this array. The result should be the sorting based on DC, WH and Store, as desired.

For enabling this, import java.util.Arrays into the UDF.

Arrays.sort(strInput);

Now, we could have sum of quantity for this input data:

int iSumQuantity = 0;

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

{

iSumQuantity = Integer.parseInt(strInput<i>.substring(12,13)); //initialize sum to current value

if (strInput<i>.substring(0, 12).equals(strInput[i+1].substring(0,12)) ) //check if next element in array has same combination of DC, WH and Store. Pls check the input parameters to substring.

{

iSumQuantity = iSumQuantity + Integer.parseInt(strInput[i+1].substring(12,13));

}

else

{

//Case when the combination changes. So we have to create a resultant value in target as a total of quantity.

Integer objSumQuantity = new Integer(iSumQuantity);

result.addValue(objSumQuantity.toString());

iSumQuantity = 0;

}

}

I hope this one should work. Or atleast should give you an idea about how this one could be achieved.

Thanks,

Bhavish

Rewards points if comments helpful

[Former Member]

Hi,

the below UDF code will successfully work for your requirment of mapping

You can use it for segments as well as for all other fields.

but for other fields change the "result.addValue(Integer.toString(qty1)); "

statement to pass value as a<i>,b<i>,c<i> etc.

Here Create UDF with cache as queue,

Then pass the fields as

DC -->a

WH-->b

Store-->c

Qty-->d

//write your code here

String DC = new String(a[0]);

String WH = new String(b[0]);

String Store = new String(c[0]);

int qty = Integer.parseInt(d[0]);

int qty1 = 0;

char flag = ' ';

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

{

if(a<i>.equals(DC)&& b<i>.equals(WH)&& c<i>.equals(Store))

{

qty = Integer.parseInt(d<i>);

qty1 = qty1 + qty;

flag = 'X';

DC = a<i>;

WH = b<i>;

Store = c<i>;

}

else

if(flag == 'X')

{

result.addValue(Integer.toString(qty1));

result.addValue("");

result.addContextChange();

flag = ' ';

qty1 = 0;

DC = a<i>;

WH = b<i>;

Store = c<i>;

--i;

}

}

result.addValue(Integer.toString(qty1));

result.addContextChange();

[Former Member]

Thanks a lot VJ, Mario and Swarup for your input. I used your logic and solved it by using a combination of Java UDF and standard function.

[Former Member]

Thanks, Regards

Mario

[VijayKonam]

Hi,

> The field names are: Field1, Filed2, DC, WH, Store,Qty.

>

> I need to consolidate this structure based on DC and then WH and then Store and then sum the Qty. The result should look like:

I assume that, its nothing but the above keys are going to identify the rows. All the ones you wanna add, would have the same values for these fields?

Concatenate all these fields in to a single field and then use node functions to generate the target field. This would generate the required number of target fields. No using the same logic add then in UDF.

I donno if it was clear enough

VJ