The only solution I can get readily is using stage variables in a transform

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

Question Posted / raj

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

Please Help Members By Posting Answers For Below Questions

What are the functionalities of link partitioner?

1022

What all the types of jobs you developed?

1181

how to sort two columns in single job in datastage.

2905

Difference between sequential file and data set?

1205

What is the difference between operational data stage (ods) and data warehouse?

1243

whom do you report?

1933

CHANGE CAPTURE

1305

What is meta stage?

1183

What is ibm datastage flow designer?

1149

Which is the best institute in Hyderabad to learn OBIEE and DataStage?Please tell me faculty name also

3167

What is process model?

1980

What is the purpose of interprocessor stage in server jobs?

2233

How to implement complex jobs in data stage?

1112

Terminate Activity

1281

Explain the situation where you have applied SCD in your project?

2040