Hi,
Please tell me how to solve this scenario in datastage ?
Here we have 3 columns in a table TEST
CODE,ENTRY DATE and BATCH
The table looks like
CODE ENTRYDATE BATCH
100 100716 1
100 100716 1
100 100716 1
200 122517 2
200 122517 2
302 555555 8
302 555555 8
302 555555 8
We need to create a seqno on grouping these 3 columns.
The result should be like this.
CODE ENTRYDATE BATCH SEQNO
100 100716 1 1
100 100716 1 2
100 100716 1 3
200 122517 2 1
200 122517 2 2
302 555555 8 1
302 555555 8 2
302 555555 8 3
Answer Posted / raj
The only solution I can get readily is using stage variables in a transformer. In order to achieve this I need to define 3 stage variables to map them to input columns. Another 3 stage variables to store the previous value. Another 3 variables to compare the values. A total of 9 stage variables are needed.
To reduce the number of stage variables, lets us checksum stage
A checksum stage generates a unique value with the given columns as input.
We supply these three columns as input and generate unique values for each combination and then use that value for comparison in the transformer
Transformer:
Stage Variable:
Checksum -> Sv1
Sv2 -> Sv1
if Sv1=Sv2 then Sv3+1 else 1 -> Sv3
Sv3 -> SEQNO
We get the desired output
Is This Answer Correct ? | 0 Yes | 0 No |
Post New Answer View All Answers
Difference between IBM DATA STAGE8.5 and DATA STAGE9.1 ?
What are routines in datastage?
what should be ensure to run the sequence job so that if its get aborted in 10th job before 9job should get succeeded?
What are the types of containers in datastage?
How to reverse the string using SQL?
describe the Steps to confiure a Qlogic switch
how to read 100 records at a time in source a) hw is it fr metadata Same and b) if metadata is nt same?
what is the use of surogate key in datastage
What is data partitioning?
Triggers,VIEW,Procedures
What could be a data source system?
What is the precedence of stage variables,derivations, and constraints?
How do you run datastage job from the command line?
How a source file is populated?
What all the types of jobs you developed?