Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello. I have the following input data samples where I need to collapse multiple rows into one row based on a key column in an output table.
Input data table:
Key, Col1, Col2, Col3
1, Name1, , ,1
1, ,Value1, ,1
1, , , Class1 ,1
2, Name2, , ,1
2, , Value2, ,1
2, , , Class2 ,1
3, Name1, , ,1
3, , Value1, ,1
3, , , Class1 ,1
Output data table:
Key, Col1, Col2, Col3
1, Name1, Value1, Class1
2, Name2, Value2, Class2
3, Name1, Value1, Class1
I attached a sample app to collapse multiple rows without any success. Does anyone have an idea on how to accomplish this task?
Thanks in advance,
Myong
Input:
Load * inline [
Key, Col1, Col2, Col3
1, Name1, , ,1
1, ,Value1, ,1
1, , , Class1 ,1
2, Name2, , ,1
2, , Value2, ,1
2, , , Class2 ,1
3, Name1, , ,1
3, , Value1, ,1
3, , , Class1 ,1 ];
NoConcatenate
Output:
Load
Key,
purgechar(Concat(Col1,','),',') as Col1,
purgechar(Concat(Col2,','),',') as Col2,
purgechar(Concat(Col3,','),',') as Col3
Resident Input
Group By Key
;
Drop Table Input;
Hi Myong, maybe with a grouped load and using maxstring:
LOAD Key,
MaxString(Col1) as Col1,
...
Group by Key;
Input:
Load * inline [
Key, Col1, Col2, Col3
1, Name1, , ,1
1, ,Value1, ,1
1, , , Class1 ,1
2, Name2, , ,1
2, , Value2, ,1
2, , , Class2 ,1
3, Name1, , ,1
3, , Value1, ,1
3, , , Class1 ,1 ];
NoConcatenate
Output:
Load
Key,
purgechar(Concat(Col1,','),',') as Col1,
purgechar(Concat(Col2,','),',') as Col2,
purgechar(Concat(Col3,','),',') as Col3
Resident Input
Group By Key
;
Drop Table Input;