Stage variable properties!!

[Madhav_M]

Hi,
I need to meet the following requirement.

cust_no telno skey_telno(generated output column in transformer )
1 12 1
1 23 2
1 NULL NULL
2 25 1
2 45 2
3 20 1
3 NULL NULL

Basically based on the input columns cust_no, telno
I need to generate skey_telno in the target table as shown above.

1) In the source stage i selected all the records order by cust_no, telno

2) In the transformer stage

In the stage variable pane:
Declare 2 stage variables tkey, tTemp.
tKey is initialized to 1, tTemp is initialized to NULL

step1:
If DSLink3.custno <> tTemp Then tkey=1 Else tkey+1
step2:
tTemp = DSLink3.custno

In the output link:
If IsNull(DSLink3.telno) Then '' Else tkey -> skey_telno


But the job results unexpected output...when new custno comes the stage variable starts with 0 rather than 1

Job output is:
cust_no telno skey_telno
1 12 1
1 23 2
1 NULL NULL
2 25 0
2 45 1
3 20 0
3 NULL NULL

is this is property of stage variable? can't it be reinitialized?

Please throw your thoughts.

Thanks
Madhav.

[Sainath.Srinivasan]

Is your transformer the very last stage before writing to the target file?

[ray.wurlod]

The expression tkey=1 evaluates to 0 unless the value of tkey is 1.

Try

Code: Select all

If DSLink3.custno <> tTemp Then 1 Else tkey+1 

[Sainath.Srinivasan]

Or must it be

Code: Select all

If DSLink3.custno <> tTemp Then tkey + 1 Else tkey 

But note that you are dealing in parallel mode.

[jasper]

I use another way to do this, which looks much more elegant( to me at least). In a sort stage (I suppose you need to sort somewhere before this anyway) you can let it generate a 'key change column'.
I usually use this, in the transformer your logic then becomes something like :
if keychange is 1 then 1 else prev+1