Split a delimited column based a value in another column

anu123 · Post by **anu123** » Mon Jun 17, 2013 3:04 pm

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

chulett · Post by **chulett** » Mon Jun 17, 2013 3:55 pm

Guessing this really doesn't have anything to do with ProfileStage, so moved your post here.

Off the top of my head, seems like the Field() function would work for this, in conjunction with transformer looping.

ray.wurlod · Post by **ray.wurlod** » Mon Jun 17, 2013 4:43 pm

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 ":").

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))

If you need the "||" characters, simply change the middle operand from "~" to "||~||".

anu123 · Post by **anu123** » Mon Jun 17, 2013 5:40 pm

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.wurlod · Post by **ray.wurlod** » Mon Jun 17, 2013 9:54 pm

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.

chulett · Post by **chulett** » Mon Jun 17, 2013 10:19 pm

Aha!

ray.wurlod · Post by **ray.wurlod** » Tue Jun 18, 2013 12:41 am

... or a routine.

chulett · Post by **chulett** » Tue Jun 18, 2013 8:05 am

Of course.

anu123 · Post by **anu123** » Tue Jun 18, 2013 10:26 am

Ray, looks like Index() take three params...Index (string, substring, instance) and you have four in there..

Can you please explain why you want me to add below one to over all logic?

Index(Sequence, ",", 1) = 0 Then

chulett · Post by **chulett** » Tue Jun 18, 2013 11:04 am

anu123 wrote:Ray, looks like Index() take three params...Index (string, substring, instance) and you have four in there..

No, he has three.

anu123 · Post by **anu123** » Tue Jun 18, 2013 11:10 am

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,

chulett · Post by **chulett** » Tue Jun 18, 2013 11:30 am

Which is what he has... look more closer, you can't just count the commas:

Index(Sequence, ",", 1)

1st parameter: Sequence
2nd parameter: ","
3rd parameter: 1

anu123 · Post by **anu123** » Tue Jun 18, 2013 11:45 am

Thank you Craig. I am stupid and was thinking it single quotes and checking for empty.......

thanks for the explaining this to me.

Anu

chulett · Post by **chulett** » Tue Jun 18, 2013 12:52 pm

Nothing stupid about missing this at all, it can be confusing when you have mixtures of quotes and commas.

anu123 · Post by **anu123** » Wed Jun 19, 2013 8:56 am

Thank you for encouraging me, Craig.