DSXchange

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

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.

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.

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

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 This will work only for one column to be pivoted. So the code should be changed to incorporate all the columns.

Hi vsi

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

Thanks