Datastage Scenario Based Question/Answer : 3

Datastage Scenario Based Question/Answer : 3

Find the missing values from source data.

   Input Data :

Col_1 Col_2

1 x 
1 y 
2 z 
3 x 
3 z

OutPut:

Col_1 Col_2
1 Z 
2 x
2 y
3 y

Find the solution for this problem.

Source->Sort -> Pivot -> Transformer->Funnel(3Links)->Target

1. Read the Source File.

2. Sort the Data on both the columns ascending.

3. Use Vertical pivot for pivoting the data.

you will get data like this.

Col1|Col2

1|XY

2|Z

3|XZ

4. In transformer create 3 stage variables.

SV1(Varchar1)=> If Len(Col2)=3 Then 1 Else 0

SV2(Varchar1)=> If Len(Col2)=2 Then (If Col2[1,2]='XY' Then 'Z' Else If Col2[1,2]='XZ' Then 'Y' Else 'X') Else '0'

SV3(Varchar1)= If Len(Col2)=1 Then (If Col2[1,1]='X' Then 'YZ' Else If Col2[1,1]='Y' Then 'XZ' Else 'XY') Else '00'

4. Create 3 Output links from Transformer.

Link1- For X (Constraint)-> SV2='X' OR SV3[1,1]='X'

Link1- For Y (Constraint)-> SV2='Y' OR SV3[2,1]='Y'

Link1- For Z (Constraint)-> SV2='Z' OR SV3[2,1]='Z'

5. Funnel all the 3 links.

6. You will get required output.