vertical pivot

[vsi]

Hai I got an input file as

123,1,a,b,c,xy
123,2,d,e,f,yz
123,3,m,n,o,yz
456,1,c,b,a,yx
456,3,r,s,t,yx

I need output as

"123",",a,b,c,xy,d,e,f,yz,m,n,o,yz"
"456",",c,b,a,yx,,,,,r,s,t,yx"

I am getting output as

"123",",a,b,c,xy,d,e,f,yz,m,n,o,yz"
"456",",c,b,a,yx,r,s,t,yx"
when i implement vertical pivot

Is there any way to do like i want

[opdas]

Hi,
You can try this.
1.Filter the input set into 3 datasets based on the second column value.
2.Merge the 3 datasets based on key column

Let us know if it works.

[kumar_s]

If you number of records per group is limited to 3, the above solutin will work out.
You can also do this with a transformer and aggregator combination. Search for your title you will get lot of post.
If you are not sure about the nubmer of records per group, writing a custom code will be your best option. I recall there is a code exist for the same.

[panchusrao2656]

Sort the data based on input.Col1

Use two stage variables svar1,svarflag.

1.svar1 <-- If left(svar1,3) = input.col1 Then svar1=svar1 : input.col2 Else col1

( Concatenate till you get a new value for input.col1 )

2.svarflag <-- If ( left(svar1,3) <> input.col1 And len(svar1) > len(input.col1) ) Then "NEW" Else "OLD"


Put a contstraint in the transformer: svarflag = "NEW"

output.col1 = svar1


I believe this should work

[kumar_s]

svar1 <-- If left(svar1,3) = input.col1 Then svar1=svar1 : input.col2 Else col1

In this case, you need to have your delimiter concatinated as well.
Like

Code: Select all

svar1 <-- If left(svar1,3) = input.col1 Then svar1= svar1:",":input.col2 Else col1 

This will work only for one column to be pivoted. So the code should be changed to incorporate all the columns.

[panchusrao2656]

Hi vsi

Can you post your logic when you are done with it.

Thanks