cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

UDF: how to get a value in the previous record when lopping over records?

Former Member

on ‎03-20-2010 9:50 AM

0 Kudos

Hello

I have this challenge:

Source message:

<row>

<ID>111</ID>

<Score>200</Score>

<Date>2010-01-01</Date>

</row>

<row>

<ID>111</ID>

<Score>230</Score>

<Date>2010-01-02</Date>

</row>

<row>

<ID>112</ID>

<Score>230</Score>

<Date>2010-01-03</Date>

</row>

etc...

Now the case is, that IF the value of ID of the current record (row) is equal to the value of the ID of the previous record AND the Score value of the current record is bigger than the Score value of the previous record THEN a record in the target message must be created with these values:

Target Message:

<row>

<ID_target> value of source ID </ID_target>

<Score_yesterday> Score value of previous record (day) </Score_yesterday>

<Score_current_date> Score value of current record </Score_current_date>

<Date_current> Date of current record </Date_current>

</row>

I am thinking of using GlobalContainer to store the previous values but am note sure how to maintain these previous values. In addition, I am thinking about using a UDF on the target row record, which will just create the row, if conditions are met (result.addValue("");) and then just make a simple mapping of the ID_target, Score_current_date and Date_current fields and make an other UDF for the Score_yesterday field where I get the Score for the previous record using the GlobalContainer value, that I stored in the other UDF.

BUT:

Should I use "All values of context" or "All values of Queues" in the UDF used on the row record and how do I make sure, that the Score_yesterday global container value is maintained correctly? I am planning of setting it in the UDF used on the Target row record but will it then be in sync with the consuming UDF of the Score_yesterday field?

Java code to go into the UDF would be very much appreciated

Mikael

Show replies
Show replies
Answer

Accepted Solutions (0)

Answers (1)

Answers (1)

justin_santhanam
Active Contributor
‎03-20-2010 3:55 PM
0 Kudos

Mikael,

Can you please confirm the below questions?

Note: I have the given below examples like : ID, SCORE

Will the data come in sorted order? (ID's are sorted followed by Scores as well)

Example:

111, 200

111,201

100,90

100, 96

and not:

111, 201

100, 90

111, 200

100, 96

Can there be more than one pair of data for the given ID's?

Example

111,200

111,201

and not:

111,200

111,201

111,203

What needs to be done if there is only one set of data?

Example

111,200

and not:

111, 200

111,201

Please answer the above questions, so it would be easy for us to suggest a concrete solution?

Thanks!

Show replies
Show replies
Former Member
‎03-20-2010 4:22 PM
0 Kudos

Hej Raj

Thank you for your response. the input file is of this structure

ID, datetime, Score (which is transformed in the sender file adapter by context conversion to an xml structure)

The IDs are thus always grouped, the datetime increses by one day within each ID and the Score can take any value.

571515198310502207,2010-03-01 00:00,1000

571515198310502207,2010-03-02 00:00,1010

571515198310502207,2010-03-03 00:00,1000

571515198310502207,2010-03-04 00:00,1050

571515198310502207,2010-03-05 00:00,1080

571515198310502207,2010-03-06 00:00,1000

571515198310502108,2010-03-01 05:00,2020

571515198310502108,2010-03-02 05:00,2000

571515198310502108,2010-03-03 05:00,2040

571515198310502108,2010-03-04 05:00,2000

571515198310502108,2010-03-05 05:00,2040

571515198310502108,2010-03-06 05:00,2000

571515198310302203,2010-03-01 00:00,3000

571515198310302203,2010-03-02 00:00,3040

571515198310302203,2010-03-03 00:00,3070

571515198310302203,2010-03-04 00:00,3000

571515198310302203,2010-03-05 00:00,3020

571515198310302203,2010-03-06 00:00,3000

I hope this is sufficient clarification

MIkael

justin_santhanam
Active Contributor
‎03-20-2010 4:47 PM
0 Kudos

Mikael,

Thanks for the clarification! So considering your below example

571515198310502207,2010-03-01 00:00,1000

571515198310502207,2010-03-02 00:00,1010

571515198310502207,2010-03-03 00:00,1000

571515198310502207,2010-03-04 00:00,1050

571515198310502207,2010-03-05 00:00,1080

571515198310502207,2010-03-06 00:00,1000

Do I need to compare only the last two records?

571515198310502207,2010-03-05 00:00,1080

571515198310502207,2010-03-06 00:00,1000

And one more thing, What I need to do if I get only one record like

571515198310502207,2010-03-06 00:00,1000

Do you need to create segment for it?

Hope you don't mind for asking so many question

Thanks!

justin_santhanam
Active Contributor
‎03-20-2010 6:04 PM
0 Kudos

Mikael,

Please see the below results. If you are ok with the results, I will let you know how to design it.

Source - XML


<?xml version="1.0" encoding="UTF-8"?>
<ns0:MT_TestingSRC xmlns:ns0="http://testing.com">
   <Row>
      <ID>100</ID>
      <SCORE>200</SCORE>
      <DATE>2010-03-19</DATE>
   </Row>
   <Row>
      <ID>100</ID>
      <SCORE>210</SCORE>
      <DATE>2010-03-20</DATE>
   </Row>
   <Row>
      <ID>101</ID>
      <SCORE>210</SCORE>
      <DATE>2010-03-01</DATE>
   </Row>
   <Row>
      <ID>101</ID>
      <SCORE>210</SCORE>
      <DATE>2010-03-02</DATE>
   </Row>
   <Row>
      <ID>1010</ID>
      <SCORE>2100</SCORE>
      <DATE>2010-03-10</DATE>
   </Row>
   <Row>
      <ID>1010</ID>
      <SCORE>2010</SCORE>
      <DATE>2010-03-11</DATE>
   </Row>
   <Row>
      <ID>1010</ID>
      <SCORE>2011</SCORE>
      <DATE>2010-03-12</DATE>
   </Row>
   <Row>
      <ID>1111</ID>
      <SCORE>2011</SCORE>
      <DATE>2010-03-12</DATE>
   </Row>
</ns0:MT_TestingSRC>

Target-XML


<?xml version="1.0" encoding="UTF-8"?>
<ns0:MT_TestingTGT xmlns:ns0="http://testing.com">
   <Row>
      <ID_TARGET>100</ID_TARGET>
      <SCORE_YDAY>200</SCORE_YDAY>
      <SCORE_CDAY>210</SCORE_CDAY>
      <CURRENT_DATE>2010-03-20</CURRENT_DATE>
   </Row>
   <Row>
      <ID_TARGET>1010</ID_TARGET>
      <SCORE_YDAY>2010</SCORE_YDAY>
      <SCORE_CDAY>2011</SCORE_CDAY>
      <CURRENT_DATE>2010-03-12</CURRENT_DATE>
   </Row>
</ns0:MT_TestingTGT>

Former Member
‎03-20-2010 11:06 PM
0 Kudos

Raj,

The result looks correct. There will always be 6 identical IDs, and every row (record) must be compared with the previous record, and if the IDs are identical, a target record must be created IF the current Score is greater than the previous Score.

Thank you very much for your swift responses on a Saturday evening!! Truely impressive!!

MIkael

justin_santhanam
Active Contributor
‎03-21-2010 2:28 AM
0 Kudos 

int tmp1;
int tmp2;
int len = ID.length;


for(int i =0;i< len;i++)
{
     if( i!= len-1)
      {
         tmp1 = Integer.parseInt(Score<i>); 
         tmp2 = Integer.parseInt(Score[i+1]);
               if(tmp2 >tmp1)
                {
		       if (TARGET[0].equals("ROW"))
                       {
                          result.addValue("");
                       }
                      else if (TARGET[0].equals("ID_TARGET"))
                      {
                          result.addValue(ID[0]);
                       }
                     else if (TARGET[0].equals("SCORE_YDAY"))
                      {
                          result.addValue(Score<i>);
                      }
                     else if (TARGET[0].equals("SCORE_CDAY"))
                     {
                         result.addValue(Score[i+1]);
                      }
                     else
                     {
                         result.addValue(DT[i+1]);
                     }
                 
                }
      }
}

I got the following results for the set of data that you provided.

Target-XML


<?xml version="1.0" encoding="UTF-8"?>
<ns0:MT_TestingTGT xmlns:ns0="http://monsanto.com/CIC">
   <Row>
      <ID_TARGET>571515198310502207</ID_TARGET>
      <SCORE_YDAY>1000</SCORE_YDAY>
      <SCORE_CDAY>1010</SCORE_CDAY>
      <CURRENT_DATE>2010-03-02 00:00</CURRENT_DATE>
   </Row>
   <Row>
      <ID_TARGET>571515198310502207</ID_TARGET>
      <SCORE_YDAY>1000</SCORE_YDAY>
      <SCORE_CDAY>1050</SCORE_CDAY>
      <CURRENT_DATE>2010-03-04 00:00</CURRENT_DATE>
   </Row>
   <Row>
      <ID_TARGET>571515198310502207</ID_TARGET>
      <SCORE_YDAY>1050</SCORE_YDAY>
      <SCORE_CDAY>1080</SCORE_CDAY>
      <CURRENT_DATE>2010-03-05 00:00</CURRENT_DATE>
   </Row>
   <Row>
      <ID_TARGET>571515198310502108</ID_TARGET>
      <SCORE_YDAY>2000</SCORE_YDAY>
      <SCORE_CDAY>2040</SCORE_CDAY>
      <CURRENT_DATE>2010-03-03 05:00</CURRENT_DATE>
   </Row>
   <Row>
      <ID_TARGET>571515198310502108</ID_TARGET>
      <SCORE_YDAY>2000</SCORE_YDAY>
      <SCORE_CDAY>2040</SCORE_CDAY>
      <CURRENT_DATE>2010-03-05 05:00</CURRENT_DATE>
   </Row>
   <Row>
      <ID_TARGET>571515198310302203</ID_TARGET>
      <SCORE_YDAY>3000</SCORE_YDAY>
      <SCORE_CDAY>3020</SCORE_CDAY>
      <CURRENT_DATE>2010-03-05 00:00</CURRENT_DATE>
   </Row>
</ns0:MT_TestingTGT>

Former Member
‎03-21-2010 12:33 PM
0 Kudos

Hi Raj

Thanks for your answer and your effort

However, I get these errors regarding Integers:

I added java.lang.Integer to the import section of the UDF to that did not fix it.

_______________

Source text of object Message Mapping:

has syntax errors:

Function DeviationCheck, Line 7:

cannot find symbol

symbol : method parseInt(int)

location: class java.lang.Integer

tmp1 = Integer.parseInt(value_input<i>);

^

Function DeviationCheck, Line 8:

cannot find symbol

symbol : method parseInt(int)

location: class java.lang.Integer

tmp2 = Integer.parseInt(value_input[i+1]);

^

2 errors

justin_santhanam
Active Contributor
‎03-21-2010 12:37 PM
0 Kudos

Mikael,

Can you post your code here? While replying in the right side of this screen you will see how to do Markup for Code. like

Thanks!

Former Member
‎03-21-2010 1:26 PM
0 Kudos

Hello again

the reason was, that I set the input variable to int and not String. Now it works

Thanks a lot Raj.

Mikael

Former Member
‎03-21-2010 1:31 PM
0 Kudos

Now I am fighting with this:


for(int i =0;i< len;i++){
	if( i!= len-1){
			tmp1 = Integer.parseInt(value_input<i>); 
			tmp2 = Integer.parseInt(value_input[i+1]);
			if (tmp2 == 0) {
						double abs_deviation = 0;
			}
			else {
						double abs_deviation = Math.abs((tmp2-tmp1)/tmp2);
			}
			if (abs_deviation > 1) {

BUT I get ths error:

Function DeviationCheck, Line 15:

cannot find symbol

symbol : variable abs_deviation

location: class com.sap.xi.tf._syv_daily_deviation_check_

if (abs_deviation > 1) {

^

former_member181985
Active Contributor
‎03-21-2010 1:34 PM
0 Kudos

It seems you are new to java language. The variable abs_deviation scope is with in the braces of if & else.

Better define the variable outside for loop.(above)

justin_santhanam
Active Contributor
‎03-21-2010 1:34 PM
0 Kudos

Mikael,

Declare abs_deviation outside the If else condition and give a try.

Thanks!

former_member181985
Active Contributor
‎03-21-2010 1:39 PM
0 Kudos

Raj,

Just want to say, good logical coding. Clearly demonstrates the power of UDFs.

Regards,

Praveen Gujjeti.

justin_santhanam
Active Contributor
‎03-21-2010 1:41 PM
0 Kudos

Thanks Praveen

Former Member
‎03-21-2010 1:58 PM
0 Kudos

Praveen: You got that right!

Ask a Question