topic Re: Collapse Multiple Rows in QlikView

Collapse Multiple Rows

Anonymous — Fri, 29 May 2015 03:37:31 GMT

I'm try to collapse multiple order lines to a single order line when loading them via a script, it's a bit hard to describe but the following should help clarify:

This is the data I'm trying to load:

Order ID	ITEM
1178	A
1178	C
1178	D
1180	B
1180	E
1195	A
1195	B
1195	C
1195	D
1108	A

And this is how I need to display it:

Order ID	Items Ordered
1178	A C D
1180	B E
1195	A B C D
1108	A

Hope this makes sense, any help would be appreciated!

Re: Collapse Multiple Rows

CELAMBARASAN — Fri, 29 May 2015 03:57:33 GMT

You could use concat function

Try like this below

Load

[Order ID],

Concat(ItemNo, ',') AS [Combined Item]

Resident Orders

Group By [Order ID];

Re: Collapse Multiple Rows

Siva_Sankar — Fri, 29 May 2015 04:01:50 GMT

use conca function in load script. find the attachment

Re: Collapse Multiple Rows

Fri, 29 May 2015 04:09:13 GMT

Hi Kevin,

Try creating a straight table with dimension as Order ID and expression Concat(ITEM,' ').

Regards,

Jemimah

Re: Collapse Multiple Rows

aveeeeeee7en — Fri, 29 May 2015 04:18:06 GMT

Use Concat() Function.

See this:

TABLE:
LOAD * INLINE [
Order ID, ITEM
1178, A
1178, C
1178, D
1180, B
1180, E
1195, A
1195, B
1195, C
1195, D
1108, A
];

CONCAT_TABLE:
LOAD
[Order ID],
Concat(ITEM,' ') AS [Items Ordered]

RESIDENT TABLE GROUP BY [Order ID];
DROP TABLE TABLE;

Hope that helps.

Regards

Av7eN

Re: Collapse Multiple Rows

Anonymous — Fri, 29 May 2015 12:05:07 GMT

Thanks for your help, I thought I needed to use concat function but wasn't sure how the Group By would work.