combining multiple rows into 1 row

[sri75]

Hi,
Can you please advice me how to achieve below logic

my source data is like this

Code: Select all

key1  key2   col1    col2    col3    col4     col5   col6   col7    col8   col9

1111   2222    **     abc     10.25
1111   2222                            $$    def     11.9     
1111   2222                                                  ##     ghi     10.4

output should be like this

Code: Select all

key1  key2   col1    col2    col3    col4     col5   col6   col7    col8   col9

1111   2222    **     abc     10.25  $$       def     11.9  ##      ghi     10.4

can we do this usiing aggregate stage instead of stage variables.

Thanks
Sri

[sri75]

sorry, my data is not looking correct

** abc 10.25 - this is first record for col1, col2 and all cother columns have spaces
$$ def 11.9 - this is second record for col 4, col5 and col6 and all other columns have spaces
## ghi 10.4 - this is third record for col7,col8 and col9

Thanks
Sri

[chulett]

Corrected.

Code: Select all

 tags preserve whitespace.

[chulett]

can we do this usiing aggregate stage instead of stage variables.

Yes, take the max of each column.

[sri75]

Thanks chulett. I tried with max, but it is giving all zeros. do I need to take other things into consideration

thanks

[sri75]

all fileds are defined as char. I need to conver from char and integer and get the max

Thanks

[priyadarshikunal]

Since you are on 8x, you can try to use "Preserve type" to prevent the conversion from char to dfloat/decimal.

[chulett]

Did you get this working? I'm curious why you thought you needed to convert anything, this should have worked fine with strings...

[greggknight]

If you're on 8.5 use the looping functionality.
or use the combine records stage under the restructure tab in the palette