Mapping Logic---Please Advice

[Former Member]

Hi All,

I have the Interface File(.TXT) -XI----File(.CSV), Source and Target both structures are same as bellow mentioned

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_Price_FTP xmlns:ns0="http://xyz.com/pi/OTC/Price_Interface">

<Recordset>

<Details>

<BOLNR/>

<ASHPDATE/>

<PAYAMOUNT/>

<FTPCODE/>

<DESCRIPT/>

</Details>

</Recordset>

</ns0:MT_Price_FTP>

I have the input file like this:

1001 2010/05/24 50 LUMB description1

1001 2010/05/24 30 LUMB description1

1001 2010/05/24 10 OTC description3

1002 2010/05/24 20 OTC description4

Output files should be

1001 2010/05/24 80 LUMB description1

1001 2010/05/24 10 OTC description3

1002 2010/05/24 20 OTC description4

Logic is, if we have the record with same Bill of lading No and FTP Code then we need to SUM the Amount and create single Record as shown in Output.

Please help me how to apply the logic in mapping

Regards

Edited by: Vamsi Krishna on May 24, 2010 4:11 PM

Edited by: Vamsi Krishna on May 24, 2010 4:13 PM

[Former Member · Accepted solution]

 I have the input file like this:

 1001    2010/05/24           50         LUMB    description1
 1001    2010/05/24           30         LUMB    description1
 1001    2010/05/24           10         OTC     description3
 1002    2010/05/24           20         OTC     description4
 
  Output files should be
 
 1001     2010/05/24           80        LUMB    description1
 1001     2010/05/24           10        OTC     description3
 1002     2010/05/24           20        OTC     description4

In order to get the above Output, you have to create two UDFs and do the mapping as shown in the links..

So, first of all lets create the UDFs..

Step 1.

UDF1 (ConactAll), is very simple with just two argument BOLNR & FTPCODE.

String output = BOLNR + FTPCODE;
return output;

UDF2 (FilterUniqueValues) is a bit complex than UDF 1. See the link to know more..

http://www.flickr.com/photos/23639237@N02/4639935942/sizes/o/

 //UDF CODE:
String[] v_current = new String [var1.length];

v_current[0] = "";

for(int i = 0; i<b.length ; i++)
{
	 if( b.length == 1 )
	{
		result.addValue( b<i> );
	}
                      else  if( var1<i>.compareTo(v_current[0]) != 0)
	{
		result.addValue( b<i> );
		 v_current[0] = var1<i>;
	}
}

Step 2.

Mapping of PAYAMOUNT field.

http://www.flickr.com/photos/23639237@N02/4639345923/sizes/o/

Update: -- In case values are not sorted then you have to use below mapping..

http://www.flickr.com/photos/23639237@N02/4640929071/sizes/o/

Now mapping of BOLNR and rest other fields will be done in same way as shown here...

http://www.flickr.com/photos/23639237@N02/4639336703/sizes/o/

Another example of ASHPDATE.

http://www.flickr.com/photos/23639237@N02/4639985000/sizes/o/

Update: -- In case values are not sorted then you have to use below mapping..

http://www.flickr.com/photos/23639237@N02/4641538152/sizes/o/

P.S. I am not a JAVA expert, if anyone has better solution than this please suggest.

Edited by: Sarvesh Singh on May 26, 2010 2:01 PM

[Former Member · Accepted solution]

Hi sarvesh,

Thanks a lot for this much effort.

I did as it is you have given, But getting output in this way, its not getting sum

Input i gave as

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_Price_FTP xmlns:ns0="http://alberto.com/pi/OTC/Price_Interface">

<Recordset>

<Details>

<BOLNR>1001</BOLNR>

<ASHPDATE>05/25/2010</ASHPDATE>

<PAYAMOUNT>50</PAYAMOUNT>

<CONDCODE>FSC</CONDCODE>

<DESCRIPT>dssada</DESCRIPT>

</Details>

<Details>

<BOLNR>1001</BOLNR>

<ASHPDATE>05/25/2010</ASHPDATE>

<PAYAMOUNT>30</PAYAMOUNT>

<CONDCODE>FSC</CONDCODE>

<DESCRIPT>dssada</DESCRIPT>

</Details>

</Recordset>

</ns0:MT_Price_FTP>

Output is

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_Price_SAP xmlns:ns0="http://alberto.com/pi/OTC/Price_Interface">

<Recordset>

<Details>

<BOLNBR>1001</BOLNBR>

<ASHPDATE>05/25/2010</ASHPDATE>

<PAYAMOUNT>50</PAYAMOUNT>

<CONDCODE>FSC</CONDCODE>

<DESCRIPT>dssada</DESCRIPT>

</Details>

<Details>

<PAYAMOUNT>30</PAYAMOUNT>

</Details>

</Recordset>

</ns0:MT_Bracket_Price_SAP>

Regards

[Former Member · Accepted solution]

From your results it seems that you have not done the mapping of Details segment in correct way. Do it as shown here..

http://www.flickr.com/photos/23639237@N02/4642135031/sizes/o/

Secondly the mapping for summing up the payamount is correct and working fine, I guess you have made some mistake either in setting the context of source field to recordset or you have not used the SplitByValue with option On value change.

Look the mapping screen shot carefully. http://www.flickr.com/photos/23639237@N02/4639345923/sizes/o/

[Former Member · Accepted solution]

Hi sarvesh,

Excellent......Working good as expected, and in one case it is like this

I have the input file like this:

1001 2010/05/24 50 LUMB description1

1001 2010/05/24 30 LUMB description1

1001 2010/05/24 10 OTC description3

1001 2010/05/24 50 LUMB description1

1002 2010/05/24 20 OTC description4

Output files is coming like this

1001 2010/05/24 130 LUMB description1

1001 2010/05/24 10 OTC description3

1002 2010/05/24 20 OTC description4

suppressed 2010/05/24 suppressed LUMB description1

Means if the same BOLNR is present after different BOLNR means, it is behaving like this

Regards

[Former Member · Accepted solution]

I am not sure why you are getting the last record as suppressed 2010/05/24 suppressed LUMB description1 because it shouldn't come at all. I have already tested this case and it works perfectly.

I hope you have mapped all your target fields (except payamount) based on below logic. Have a second look at mapping logic and function used in mapping screen shot. May be you have missed something.

http://www.flickr.com/photos/23639237@N02/4641538152/sizes/o/

[Former Member · Accepted solution]

Hi Vamsi,

I have a work around for you...Please take a look on following screenshots.

NOte: In my case I have 4 key values,based on that I'm adding quantites. You need to follow the same logic, but you need have only 2 key values.

Root Node:

http://www.flickr.com/photos/50573512@N06/4642413919/

BOLNR NOde:

http://www.flickr.com/photos/50573512@N06/4642413925/

Quantity NOde:

http://www.flickr.com/photos/50573512@N06/4642413935/

Please have a try and let me know.

-Esha

[Former Member · Accepted solution]

Hi Sarvesh,

These are the mappings for each field

1) Mapping of Details Segment

http://img535.imageshack.us/img535/6957/detailsu.jpg

2) Mapping of BOLNBR

http://img241.imageshack.us/img241/5691/bolnbr.jpg

3)Mapping of Date

http://img39.imageshack.us/img39/3750/datec.jpg

4) Mapping of Amount

http://img210.imageshack.us/img210/9932/payamount.jpg

5) Mapping of CODE

http://img687.imageshack.us/img687/5260/codesc.jpg

6) Mapping of DESC

http://img405.imageshack.us/img405/44/descqi.jpg

Set the Context of all source fields in all the mappings to Recordset

Regards

[Former Member · Accepted solution]

Now I see what you did wrong here...

Your Date, code, dec should be mapped EXACTLY in same manner as you mapped BOLNR.

Update:- e.g. Date should be mapped like this.. http://www.flickr.com/photos/23639237@N02/4642540159/sizes/o/

Similarly map FTPCODE & Description.

Edited by: Sarvesh Singh on May 27, 2010 2:48 AM

[Former Member · Accepted solution]

Hi sarvesh,

I did as you mentioned for the remaining fields also.

Now working as expected but, extra Details node is coming as shown here

http://img35.imageshack.us/img35/2346/extradetails.jpg

Regards

[Former Member · Accepted solution]

Map the Details node as shown in below link..

http://www.flickr.com/photos/23639237@N02/4643211516/sizes/o/

[Former Member · Accepted solution]

WOW Sarvesh.....You are Excellent . Working like charm...

Thanks Thanks a lot Buddy.....

When i execute the interface end to end. My Mapping changes are not reflecting.

Activated the change list and did SLD Cache refresh then did SXI_CACHE refresh too

But no Use. ..

Regards

Vamsi Krishna

[Former Member · Accepted solution]

Vamsi, good to know you finally managed to get your desired result.

I suggest you to close this thread and open new one to solve your next problem. Plz do not mix two problems together.

Thanks!

[Former Member · Accepted solution]

Thought to open new one but...... tried to ask here itself )))

Will open new one

Regards

[Former Member · Accepted solution]

Did you Clear the cache Notifications too?

Regards

Ramg

[Former Member · Accepted solution]

You know why I said so.. there are mainly 3 reasons..

1. We should always ask the new question in separate thread as per SDN rules of engagements.

2. Other people on SDN can also give their replies.. and

3. People facing the same issue can take a benefit of it..

So thanks for you understanding and always search before you ask any question.

[Former Member · Accepted solution]

I got your valuable point Sarvesh. No problem. Thank you for the guidance

Thanks a lot

Regards

Vamsi

[Former Member]

Hi Vamsi,

This is just an idea

Check this code take advanced (QUEUE) type UDF

input as str1[], str2[], str3[], str4[], str5[]

String temp;

for(int i=0;i<str1.length-1; i++)

{

temp = "";

if ((str4<i>.equals(str4[i1]) && (str5<i>.equals(str5[i1]))

{

temp = str3<i> + str3[i+1];

result.addValue(str1<i>+ str2<i> + temp + str4<i> +str5<i>);

i++;

}

else result.addValue(str1<i>+ str2<i> + str3<i>+ str4<i> +str5<i>);

}

Regards

Ramesh

[Former Member]

Hi Ramesh,

Thanks a lot for your great idea and UDF, let me try this

and if i use these 5 fields from source as input to UDF and where should i mapp this UDF to target??

To the root node???

Regards

[Former Member]

Hi Vamsi,

Here you have to use 5 UDFs for each individual field.

for(int i=0; i<arr1.length - 1; i++)

{

if(str4<i>.equals(str4[i1]) && && (str5.equals(str5i1))

{

result.addValue(str1<i>);

i++;

}

else result.addValue(str1<i>);

}

for 2nd, 4th and 5th fields are also same logic except result.addValue(str2<i>), result.addValue(str4<i>);

result.addValue(str5<i>);

for 3rd field

for(int i=0; i<arr1.length - 1; i++)

{

temp = "";

if(str4<i>.equals(str4[i1]) && && (str5.equals(str5i1))

{

temp = str3<i> + str3[i+1];

result.addValue(temp);

i++;

}

else result.addValue(str3<i>);

}

Regards

Ramesh

[Former Member]

Hi Ramesh,

Thanks so much, let me try this and tel you the result of this

I think u are checking for only FTP Code value, but i need to compare the Bill of lading No too..

Regards

Edited by: Vamsi Krishna on May 24, 2010 6:17 PM

[Former Member]

Hi ramesh,

Could you pls update this logic, i need to check for Bill of lading No and FTP Code

Regards

Edited by: Vamsi Krishna on May 25, 2010 5:03 PM

[Former Member]

Hi Vamsi,

you want to compare the first and fourth fields so in the comparison check this values.

But if you use this UDF you may not get the output in a single row i guess.

for(int i=0; i<arr1.length - 1; i++)

{

if(str4.equals(str4[i1]) && (str1.equals(str1[i1]))

{

result.addValue(str1);

i++;

}

else result.addValue(str1);

}

for 2nd, 4th and 5th fields are also same logic except result.addValue(str2), result.addValue(str4);

result.addValue(str5);

for 3rd field

for(int i=0; i<arr1.length - 1; i++)

{

temp = "";

if(str4.equals(str4[i1]) && (str1.equals(str1[i1]))

{

temp = str3 + str3i+1;

result.addValue(temp);

i++;

}

else result.addValue(str3);

}

Logic : first we are checking the first and fourth field values (str1.equals(str1[i+1])) if it is equal then sending the same value and incrementing the i value in the loop as well as for loop so second record doesn't come, so it checks the next record.

Regards

Ramesh

[Former Member]

Hi Ramesh,

>>But if you use this UDF you may not get the output in a single row i guess.

What do u mean by Single row?? i just want this way

If we have 5 records matched in file for that condition means, we need to add the bellow 4 amounts to first record and ignore the remaining 4 bellow records

Regards

[Former Member]

Hi Vamsi,

> >>But if you use this UDF you may not get the output in a single row i guess.

> What do u mean by Single row?? i just want this way

Sorry for this statement.

You can check the code, i think that should work.

Regards

Ramesh