Page 1 of 1
combining multiple rows into 1 row
Posted: Wed Apr 27, 2011 5:24 pm
by 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
Posted: Wed Apr 27, 2011 5:27 pm
by 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
Posted: Wed Apr 27, 2011 5:58 pm
by chulett
Corrected.
Re: combining multiple rows into 1 row
Posted: Wed Apr 27, 2011 5:59 pm
by chulett
sri75 wrote:can we do this usiing aggregate stage instead of stage variables.
Yes, take the max of each column.
Posted: Wed Apr 27, 2011 7:40 pm
by sri75
Thanks chulett. I tried with max, but it is giving all zeros. do I need to take other things into consideration
thanks
Posted: Wed Apr 27, 2011 8:11 pm
by sri75
all fileds are defined as char. I need to conver from char and integer and get the max
Thanks
Posted: Tue May 03, 2011 6:10 am
by priyadarshikunal
Since you are on 8x, you can try to use "Preserve type" to prevent the conversion from char to dfloat/decimal.
Posted: Tue May 03, 2011 6:34 am
by chulett
Did you get this working? I'm curious why you thought you needed to convert anything, this should have worked fine with strings...
Posted: Tue May 03, 2011 7:45 am
by greggknight
If you're on 8.5 use the looping functionality.
or use the combine records stage under the restructure tab in the palette