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 !
Some issues in Duplicate records
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 334
- Joined: Fri Dec 01, 2006 5:17 am
- Location: Texas
Re: Some issues in Duplicate records
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]
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]