Need Help to set the counter

[rcil]

Hello All,

I need some inputs in completing the task below.

Code: Select all

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.

[kumar_s]

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

[rcil]

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

[kumar_s]

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

[rcil]

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

[kumar_s]

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'