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.
Populating Non-null Values in sequential manner in Target
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 26
- Joined: Tue Oct 23, 2007 4:07 am
- Location: Blore,India
-
- Participant
- Posts: 26
- Joined: Tue Oct 23, 2007 4:07 am
- Location: Blore,India
Populating Non-null Values in sequential manner in Target
Sorry the 12th column in the source is not AccAddress1 it's AccAddress6
asdfasdf
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?
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?
2 B 1 4 ALL
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 26
- Joined: Tue Oct 23, 2007 4:07 am
- Location: Blore,India