DSXchange

There is some issue while populating the records in the target table.
We have 2 columns in the source table EVENT_ID,EVENT_DATE
The records are as follows:
EVENT_ID EVENT_DATE
1,27-MAR-08
1,27-MAR-08
1,27-MAR-08
2,27-MAR-08
2,27-MAR-08

Now we want to populate target table with one more exract column which is nothing but transaction number(number of transactions done)..means number of occurance of EVENT_ID.
that is we want to generate number as:
EVENT_ID EVENT_DATE TRANSACTION_DONE
1,27-MAR-08,1
1,27-MAR-08,2
1,27-MAR-08,3
2,27-MAR-08,1
2,27-MAR-08,2

Means in all 3 transactions are done by EVENT_ID 1.We want to store all these three numbers.
Now my question is how can we generate TRANSACTION_DONE column?
In our job we are using sort and transfomer stage.

Thanks !

Hi

First you have to sort by event id and in stage variables u have to specify 3 variables i.e., x,y,z

x--- if event_id=y z=1 else 1
y--- event_id
z--- x

try this.........

Regards
Dinesh Y

sorry


x--- if event_id=y z+1 else 1

_________________

IF you can use a join and aggregator stage, then

1. Create a copy of input records in 2 streams.

2. on 1st stream, aggregate on event id to get the max value of transactions (MAX_TRANS) done.

3. join 2nd stream from step 1 with the results of step 2, to attach the MAX_TRANS for each event id.

4. Define the following transformer stage variables -
Variable name Initial Value Derivation
----------------- ------------ --------------------------------
SVarConcat EVENT_ID

SVarTemp1 ' ' if SVarTemp3 <> ' ' Then '*' Else ' '

SVarTemp2 if SVarTemp1 = ' ' OR SVarConcat <>
SVarTemp3 then 1 else SVarCounter

SVarCounter 1 if SVarCounter = MAX_TRANS then 1 else SVarCounter + 1

SVarTemp3 ' ' EVENT_ID


Assign the value of the variable SVarTemp2 to the destination field TRANSACTION_DONE[/i]

x => if event_id=y Then x+1 else 1
No need for variable z as well.