Collapse Multiple Rows into one row based on a key

[Anonymous]

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

[rubenmarin]

Hi Myong, maybe with a grouped load and using maxstring:

LOAD Key,

  MaxString(Col1) as Col1,

...

Group by Key;

[Digvijay_Singh · Accepted solution]

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;

[Digvijay_Singh]