Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Collapse Multiple Rows into one row based on a key

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

1 Solution

Accepted Solutions
Digvijay_Singh

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;

View solution in original post

3 Replies
rubenmarin

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

LOAD Key,

  MaxString(Col1) as Col1,

...

Group by Key;

Digvijay_Singh

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;