topic Re: Collapse Multiple Rows into one row based on a key in QlikView

Collapse Multiple Rows into one row based on a key

Anonymous — Wed, 31 May 2017 21:09:42 GMT

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

Re: Collapse Multiple Rows into one row based on a key

rubenmarin — Wed, 31 May 2017 21:35:17 GMT

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

LOAD Key,

MaxString(Col1) as Col1,

...

Group by Key;

Re: Collapse Multiple Rows into one row based on a key

Digvijay_Singh — Wed, 31 May 2017 21:41:37 GMT

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;

Re: Collapse Multiple Rows into one row based on a key

Digvijay_Singh — Wed, 31 May 2017 21:42:53 GMT