DSXchange

Posted: **Wed Mar 29, 2006 6:05 pm**

Hello All,

I need some inputs in completing the task below.

Source:
MNO        PNO     SCODE   CNT
--------- --------- ----- -----
MM0051789 PR0021989 88 	10
MM0051789 PR0021989 09 	20
MM0051789 PR0021989 90 	15
MM0051789 PR0021989 60 	10
MM0053322 PR0022244 09 	10
MM0053322 PR0022244 09 	22

I have to compare MNO and PNO with the previous MNO and PNO. If they are equal then I have to add up the count. But if scode = 09 then I have to break the continuity and add the count up to that 09 record.

I need help in setting the counter to stop right at scode = 09 and continue the count with the rest. I am successful in comparing the current and previous rows using the stage variables and also making the prev Count to 0.

Code: Select all

Source:
MNO        PNO     SCODE   CNT
--------- --------- ----- -----
MM0051789 PR0021989 88 	0
MM0051789 PR0021989 09 	30
MM0051789 PR0021989 90 	0
MM0051789 PR0021989 60 	25
MM0053322 PR0022244 09 	10
MM0053322 PR0022244 09 	22

Thanks for all the help.

Posted: **Wed Mar 29, 2006 8:44 pm**

Use stage variable method.

Code: Select all

MNO_CUR = Inlink.MNO
PNO_CUR = Inlink.PNO
SCODE_CUR = Inlink.SCODE
CNT = Inlink.CNT 
CNT_TOTAL = If (MNO_CUR = MNO_PRV And PNO_CUR = PNO_PRV And SCODE_CUR <> 09) CNT_TOTAL + Inlink.CNT Else 0
MNO_PRV = MNO_CUR
PNO_PRV = PNO_CUR

Posted: **Wed Mar 29, 2006 9:18 pm**

Thanks very much for the help. I will try the provided code and get back as I don't have access to datastage now.

Does your code provide the expected results for the last two records scenario? I am asking this because even if the current and previous records are same as scode = 09 those records should not be added together.

thanks

Posted: **Wed Mar 29, 2006 9:20 pm**

I havnt checked Current and previous SCODE but only SCODE <> 09.

Posted: **Wed Mar 29, 2006 9:25 pm**

kumar_s wrote:I havnt checked Current and previous SCODE but only SCODE <> 09.

Can you please provide your values inputs on how to achieve the last two records scenario?

thanks

Posted: **Wed Mar 29, 2006 9:40 pm**

rcil wrote:
kumar_s wrote:I havnt checked Current and previous SCODE but only SCODE <> 09.
Can you please provide your values inputs on how to achieve the last two records scenario?

thanks

As long as the current SCODE = 09, the value for CNT_TOTAL will be 0. Which will be acheived by the stage variable condition 'CNT_TOTAL = If (MNO_CUR = MNO_PRV And PNO_CUR = PNO_PRV And SCODE_CUR <> 09) CNT_TOTAL + Inlink.CNT Else 0'

DSXchange

Need Help to set the counter

Need Help to set the counter