Incrementing values in datastage going wrong

[hcdiss]

hHi,

INPUT -->

OLDKEY |

1234
1234
1234
1111
2345
2345


Output required --->

OLDKEY | NUMBER

1234 | 001
1234 | 002
1234 | 003
1111 |001
2345 |001
2345 | 002

What i am doing is using a stage variable which stores

SvCurrentOLDKEY -- Stores current oldkey
Sv PreviousOLDKEY -- Stores the previous key
SvNUMBER -- If currOLDKEY = prevOLDKEY then SvNUMBER + 1 else 001

I have initialised SvNumber as 000

bUT the o/p i am getting is first two same records are coming as 001.
what can be wrong.?
Order of stage variables?? or something else.

[chulett]

Yes, the order. Set the "previous" value last.

[Sainath.Srinivasan]

Being PX, you might want to consider where the data goes.

[girija]

Try this :

Code: Select all

if link.key <> stgOldKey THEN 1 ELSE stgCount + 1 ===> stgCount
link.key ===> stgOldKey

1. Partition and order input link by key.

2. Keep the order when you defined the stage variable.

3. stgCount as Number and stgOldKey as Varchar/Number ( based on your input field type)

[chulett]

Still... regardless, for this technique to have any chance of working when you compare current and previous values, you need to set the 'previous' value last or they'll always be the same.

[hcdiss]

Thanks everyone but still no luck.

What i dont understand is why the 1st two records are coming 001 while rest are being numbered correctly.

i have initialised stage variable as 0

[girija]

Still... regardless, for this technique to have any chance of working when you compare current and previous values, you need to set the 'previous' value last or they'll always be the same. ...

Hi Craig,
Please let me know if I am wrong.

As I know all stage variables and derivation execute one after another according to the order as they defined. When you compare in the first stage variable (stgCount) you have the current value in link.key but it contain the last value in stgOldKey (since it not executed yet). So I think it will work. I tried it for several time and worked fine.

Please let me know.

[chulett]

Yes, of course, as you are comparing before resetting the 'previous' value, something it appears the OP was not doing.

[girija]

Thanks Craig.

Sajid,

Did you implement what we are suggesting?

Please let us know. It is working fine in my PX environment.

[Harshavardhan]

First make sure that all your stage variables are in order, dont initialize stage variable to "0" make it "1" and then it should work fine.


If (Sgnew= Sgold) Then (Sgnum+001) Else 001 ==> sgdev

If (Sgdev= Sgnum+001) Then Sgnum+ 001 Else 001 ==> sgnum



sgdev is NUMBER.



Best Regards,
Harsha.

[Harshavardhan]

First make sure that all your stage variables are in order, dont initialize stage variable to "0" make it "1" and then it should work fine.


If (Sgnew= Sgold) Then (Sgnum+001) Else 001 ==> Sgdev

If (Sgdev= Sgnum+001) Then Sgnum+ 001 Else 001 ==> Sgnum



Sgdev is NUMBER.



Best Regards,
Harsha.

[siauchun84]

Why not using the Create Cluster Key Change Column in Sort Stage?
It will return the numbering for the repeated records.

Please correct me if I was wrong.

[srinivas.g]

use the Create Cluster Key Change Column in Sort Stage and

in transformer take stage variable apply below logic
If DSLink8.clusterKeyChange=1 Then DSLink8.clusterKeyChange Else val+1 --->val

problem solved........

[santhooosh.c]

Pls,
Try 1:
change the Execution Mode to "Sequential" in Transformer

Try 2:
change the Execution Mode to "Sequential" in Transformer
change the prev stage Execution Mode to "Sequential"

Try 3:
If above is not working, have a sort stage before your Transformer stage.

Set both stage Execution Mode to "Sequential".

Hope this helps you.