Split a delimited column based a value in another column
Moderators: chulett, rschirm, roy
Split a delimited column based a value in another column
I want to split values in column CODES and produce output based the numbers/order listed in 3rd column SEQUENCE. Below is an example of input and output I need to produce. Please help to find a solution for this.
Input:
Num|Codes|Sequence
101|a,b,c|2,3
102|m,n,o,p|3,4
103|x,y,z|3
Required Output:
Num|Codes_to_review
101|b||'~'||c
102|o||'~'||p
103|z
Thanks in advance,
Anu
Input:
Num|Codes|Sequence
101|a,b,c|2,3
102|m,n,o,p|3,4
103|x,y,z|3
Required Output:
Num|Codes_to_review
101|b||'~'||c
102|o||'~'||p
103|z
Thanks in advance,
Anu
Thank you,
Anu
Anu
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Doesn't need looping. I'm also assuming that you're intending to show concatenation with your use of "||" (though the DataStage concatenation operator is ":").
If you need the "||" characters, simply change the middle operand from "~" to "||~||".
Code: Select all
If Index(Sequence, ",", 1) = 0 Then Field(Codes, ",", Sequence, 1) Else Field(Codes, ",", Field(Sequence, ",", 1, 1)) : "~" : Field(Codes, ",", Field(Sequence, ",", 2, 1))
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Thank you Ray & Craig for quick response.
Ray, you are right, I want to concatenate.
Is there any way to handle varying count in sequence col, I mean, I can have two sequences in row-1 and four sequences in second row?
Or do I need to code for Max no. of Sequence and then trim '~'?
Thanks,
Thank you.
Ray, you are right, I want to concatenate.
Is there any way to handle varying count in sequence col, I mean, I can have two sequences in row-1 and four sequences in second row?
Or do I need to code for Max no. of Sequence and then trim '~'?
Thanks,
Thank you.
Thank you,
Anu
Anu
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I read the specification as you wanting just two (or one).
Obviously a more generic solution will be more complex. If it's to be no
more than four you could probably stick with the If..Then..Else construct.
Otherwise you are probably going to need some kind of looping solution.
Obviously a more generic solution will be more complex. If it's to be no
more than four you could probably stick with the If..Then..Else construct.
Otherwise you are probably going to need some kind of looping solution.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Craig,
As per Ray's response....
If Index(Sequence, ",", 1) = 0 Then Field(Codes, ",", Sequence, 1) Else Field(Codes, ",", Field(Sequence, ",", 1, 1)) : "~" : Field(Codes, ",", Field(Sequence, ",", 2, 1))
But as per the manual, Index() takes just three inputs in total as below.
Index (string, substring, instance).
Thanks,
As per Ray's response....
If Index(Sequence, ",", 1) = 0 Then Field(Codes, ",", Sequence, 1) Else Field(Codes, ",", Field(Sequence, ",", 1, 1)) : "~" : Field(Codes, ",", Field(Sequence, ",", 2, 1))
But as per the manual, Index() takes just three inputs in total as below.
Index (string, substring, instance).
Thanks,
Thank you,
Anu
Anu