Splitting single row into multiple rows with Delimiter '|'

kpushkaraj · Participant Joined: 20 Feb 2011 Posts: 4 Points: 35

Hi

I/P

COL1 COL2
123 1066|1084|2849|2849.......
456 1|2|ADF|....

O/P desired

COL1 COL2
123 1066
123 1084
123 2849
123 2849
456 1
456 2
456 ADF

I was trying to do this in TR with feild function and stage variable i cant get it , Can you guys please help

Thanks in advance

chulett · Posted: Tue Dec 06, 2011 10:00 am

Please show us what you tried and what isn't working about it. Also let us know if you have 8.5 or higher as that means there is a 'looping' solution for this.

kpushkaraj · Participant Joined: 20 Feb 2011 Posts: 4 Points: 35

I am using the 8.1

SV1 = COL1

SV2 =If COl1 <> SV1 Then Field(DSLink14.BKEY,'|', ) Else SV2

something like this , but i dont think this is the reight approach .Can you please guide me to right way

chulett · Posted: Tue Dec 06, 2011 11:09 am

It's not until you get to 8.5 that you can create multiple output records from one input record, which is the 'looping' that I mentioned. Before that, what you need to do is turn your one big delimited column into separate columns and you can use the Field() function for that or the Column Import stage, I do believe. Once you have all of the values into individual columns you can use the Pivot stage to do a horizontal (columns to rows) pivot to create multiple rows per 'key' value.

You will need to know the maximum possible values in your delimited string and code for that, then remove any 'empty' rows post pivot.

kpushkaraj · Participant Joined: 20 Feb 2011 Posts: 4 Points: 35

I dont know what my COL2 data length will be ?

It may be from 123|345fs|are45| 2423| ....n It's not fixed no.of columns.

chulett · Posted: Tue Dec 06, 2011 2:24 pm

I understand but you need to pick a maximum and work with that. Profile the data you are receiving and see what the max occurance is and then use something higher to be safe. That or upgrade to 8.5 where all those shenanigans become unnecessary.

ray.wurlod · Posted: Tue Dec 06, 2011 3:04 pm

There is a simple solution using a server job, in which you convert the "|" characters to value marks then write to a hashed file. Then you read back from the hashed file normalized on the multi-valu ...

What's this?

marchana · Participant Joined: 13 Jul 2010 Posts: 18 Points: 144

Here is the way i did,

From the database itself, i took one more column which contains the number generated by row_number () function limited to the length of the column data.

id, data, no
1, ad|fh|ju, 1
1, ad|fh|ju, 2
1, ad|fh|ju, 3...........

then i took count of the data column with respect to the delimiter and i put condition in the transformer no <= count .Finally i used field function to get the data separately since we know that single coulmn has to be loaded with the value.

Hope this will helpful for you.

Kirtikumar · Posted: Tue Dec 20, 2011 2:58 am

Assuming this is coming from a sequential file and " " (space) is delimiter between Id and the | delimited string, we can use the following script.

Code:

cat FileName.txt | awk -F "|" ' { FirstCol=substr($1,1,index($1, " ")-1); print $1; for(i=2;i<=NF;i++) printf("%s %s\n",FirstCol,$i); } '

Even if the input is from other source like DB, Dataset etc, we can use the one of the DS stage which allows to run Unix command on standard input. I think it is - External filter stage.

vamsi.4a6 · Participant Joined: 22 Jan 2012 Posts: 233 Points: 1998

Kirtikumar wrote:

Even if the input is from other source like DB, Dataset etc, we can use the one of the DS stage which allows to run Unix command on standard input. I think it is - External filter stage.

I hope we can not use above unix command in External filter stage.Please correct me if i am wrong?

boggavarapu · Participant Joined: 27 Jul 2009 Posts: 15 Points: 104

I tried this and got the solution.

I first used field function to split the data in col2 to different columns.

then i used pivot stage to get the desired out put..

SEQ>>TRF>>PVT>>SEQ

TRF:
Field(DSLink3.col2,',',1)
Field(DSLink3.col2,',',2)
Field(DSLink3.col2,',',3)
PVT:
col2:= col2,col3,col4

ray.wurlod · Posted: Sat May 12, 2012 4:49 pm

How do you handle the arbitratry number of values?

rameshrr3 · Posted: Tue May 15, 2012 11:14 am

I know the variable pivot was discussed in an earlier post .
It can be done perfectly if one knows the Maximum number of delimited fields possible in this file . ( It can also be gotten as teh output of a shell script or prior job that counts the delimiters for the "Sub record " field Smile

Then use a row generator that generates an integer cycle ( which cycles from 1 to Max Delim count ) for each row, combine it with the input data using a key col with data = "1" , and for each row use field function with delim instance specified by current sequence value from row generator , apply constraints in transformer such that rowgen seq value <= max delim count from delimited field) ,it should work. For server jobs use approach Ray suggested ( havent tried the server job approach myself, but the parallel job approach has worked well for me )