DSXchange

Hi,

My requirement is as below in datastage. It would be helpful if any one could gimme the solution.

In target I have 6 columns(Line1,Line2,Line3,Line4,Line5,Line6), which need to take the non-null values of the source columns in a sequence.

Case 1:Source Values are like below:
AccName1 ---- ABC
AccName2 ---- DEF
AccName3 ---- Null
AccName4 ---- Null
AccName5 ---- Null
AccName6 ---- Null
AccAddress1---Null
AccAddress2---Null
AccAddress3---Null
AccAddress4---Null
AccAddress5---XYZ
AccAddress1---GHI

Target Fields should be populated as below
Line1---ABC
Line2---DEF
Line3---XYZ
Line4---GHI
Line5---Null
Line6---Null

Case 2:Source Values are like below:
AccName1 ---- Null
AccName2 ---- DEF
AccName3 ---- Null
AccName4 ---- Null
AccName5 ---- Null
AccName6 ---- STU
AccAddress1---Null
AccAddress2---Null
AccAddress3---Null
AccAddress4---Null
AccAddress5---XYZ
AccAddress1---Null

Target Fields should be populated as below
Line1---DEF
Line2---STU
Line3---XYZ
Line4---Null
Line5---Null
Line6---Null


The similar way The target values should be assigned a non-null value from source as per the availability in a sequential manner.

Sorry the 12th column in the source is not AccAddress1 it's AccAddress6

1) Use below derivation in Stage Variable "RESVAL"
Trim(
NullToEmpty(Input.AccName1) : '|' :
NullToEmpty(Input.AccName2) : '|' :
NullToEmpty(Input.AccName3) : '|' :
NullToEmpty(Input.AccName4) : '|' :
NullToEmpty(Input.AccName5) : '|' :
NullToEmpty(Input.AccName6) : '|' :
NullToEmpty(Input.AccAddress1) : '|' :
NullToEmpty(Input.AccAddress2) : '|' :
NullToEmpty(Input.AccAddress3) : '|' :
NullToEmpty(Input.AccAddress4) : '|' :
NullToEmpty(Input.AccAddress5) : '|' :
NullToEmpty(Input.AccAddress6), "|", "R")

2) At target column derivations you can use below derivations
Line1 <-- If Field(RESVAL,'|',1) <> "" Then Field(RESVAL,'|',1) Else SetNull()
Line2 <-- If Field(RESVAL,'|',2) <> "" Then Field(RESVAL,'|',2) Else SetNull()
Line3 <-- If Field(RESVAL,'|',3) <> "" Then Field(RESVAL,'|',3) Else SetNull()
Line4 <-- If Field(RESVAL,'|',4) <> "" Then Field(RESVAL,'|',4) Else SetNull()
Line5 <-- If Field(RESVAL,'|',5) <> "" Then Field(RESVAL,'|',5) Else SetNull()
Line6 <-- If Field(RESVAL,'|',6) <> "" Then Field(RESVAL,'|',6) Else SetNull()


Let me know the result?

Ah, the magic word - "gimme".

Hi Laxman,

Thanks for your solution.