topic Re: Set Analysis with Concat in QlikView

Set Analysis with Concat

Fri, 17 Jun 2016 13:07:43 GMT

Hello. I have a chart (straight table) with columns region, warehouse, item and quantity. I'm trying to add a new column that shows, for each item, a concatenation of all warehouses that have the item in stock (Qty > 0). I'm getting hung up in the Aggr function or set analysis. The list keeps changing when different selections are made. Can someone have a look? See attached. Thanks.

Re: Set Analysis with Concat

sunny_talwar — Fri, 17 Jun 2016 13:09:45 GMT

Do you need this?

Expression:

If(Sum(Qty) > 0, Concat({<Region=,Warehouse={"=sum({<Region=,Warehouse=>} Qty)>0"}>} Warehouse, ','))

Re: Set Analysis with Concat

MK_QSL — Fri, 17 Jun 2016 13:12:02 GMT

Can you give one example what you thing wrong here?

Re: Set Analysis with Concat

swuehl — Fri, 17 Jun 2016 13:16:47 GMT

Try something like

If(Sum(Qty) > 0,

Concat(TOTAL<Item> {<Region=,Warehouse={"=sum({<Region=,Warehouse=>} Qty)>0"}>} Warehouse, ','))

Region	Warehouse	Item	Qty	Warehouses with Stock
			72
North America	Houston	A1	5	Houston,Montreal,Paris,Warsaw
North America	Houston	B6	2	Houston,Montreal,Paris
North America	Houston	E3	1	Houston,Paris,Warsaw
North America	Houston	G99	3	Houston,Montreal,Warsaw
North America	Houston	Z8	2	Houston,Montreal,Paris,Warsaw
North America	Montreal	A1	9	Houston,Montreal,Paris,Warsaw
North America	Montreal	B6	2	Houston,Montreal,Paris
North America	Montreal	D9	4	Montreal,Warsaw
North America	Montreal	G99	1	Houston,Montreal,Warsaw
North America	Montreal	Z8	2	Houston,Montreal,Paris,Warsaw
Europe	Paris	A1	4	Houston,Montreal,Paris,Warsaw
Europe	Paris	B6	2	Houston,Montreal,Paris
Europe	Paris	Z8	1	Houston,Montreal,Paris,Warsaw
Europe	Warsaw	A1	12	Houston,Montreal,Paris,Warsaw
Europe	Warsaw	C2	3	Houston,Montreal,Warsaw
Europe	Warsaw	D9	4	Montreal,Warsaw
Europe	Warsaw	E3	5	Houston,Paris,Warsaw
Europe	Warsaw	Z8	10	Houston,Montreal,Paris,Warsaw

Re: Set Analysis with Concat

Fri, 17 Jun 2016 13:18:40 GMT

Item E3 should show "Houston,Warsaw" for all E3 lines regardless of what's selected. Houston, Paris and Warsaw all stock the item E3, but only Houston and Warsaw have stock (Qty > 0). C2 should show only "Warsaw". Etc.

Re: Set Analysis with Concat

swuehl — Fri, 17 Jun 2016 13:21:32 GMT

If(Sum(Qty) > 0,

Concat(TOTAL<Item> Aggr( If(sum({<Region=,Warehouse=>} Qty)>0, Warehouse), Item, Warehouse), ','))

Region	Warehouse	Item	Qty	Warehouses with Stock
			72
North America	Houston	A1	5	Houston,Montreal,Paris,Warsaw
North America	Houston	B6	2	Houston,Montreal,Paris
North America	Houston	E3	1	Houston,Warsaw
North America	Houston	G99	3	Houston,Montreal
North America	Houston	Z8	2	Houston,Montreal,Paris,Warsaw
North America	Montreal	A1	9	Houston,Montreal,Paris,Warsaw
North America	Montreal	B6	2	Houston,Montreal,Paris
North America	Montreal	D9	4	Montreal,Warsaw
North America	Montreal	G99	1	Houston,Montreal
North America	Montreal	Z8	2	Houston,Montreal,Paris,Warsaw
Europe	Paris	A1	4	Houston,Montreal,Paris,Warsaw
Europe	Paris	B6	2	Houston,Montreal,Paris
Europe	Paris	Z8	1	Houston,Montreal,Paris,Warsaw
Europe	Warsaw	A1	12	Houston,Montreal,Paris,Warsaw
Europe	Warsaw	C2	3	Warsaw
Europe	Warsaw	D9	4	Montreal,Warsaw
Europe	Warsaw	E3	5	Houston,Warsaw
Europe	Warsaw	Z8	10	Houston,Montreal,Paris,Warsaw

If you want to replace the Aggr() with set analysis, you would need to create a key in your script made of Warehouse and Item, so you can use the advanced search for Qty > 0 on this key.

Re: Set Analysis with Concat

Fri, 17 Jun 2016 13:22:27 GMT

Thanks, but not quite. It shows different warehouses for E3 for different selections. See the E3 example I posted above.

Re: Set Analysis with Concat

sunny_talwar — Fri, 17 Jun 2016 13:23:58 GMT

Try this:

Concat({<Region>}TOTAL <Item> Aggr(If(Sum(Qty) > 0, Only({<Region=,Warehouse={"=sum({<Region=,Warehouse=>} Qty)>0"}>} Warehouse)), Region, Warehouse, Item), ', ')

Re: Set Analysis with Concat

swuehl — Fri, 17 Jun 2016 13:24:57 GMT

If(Sum(Qty) > 0, Concat({<Warehouse,Item >} TOTAL<Item> Aggr( If(sum({<Region=,Warehouse=>} Qty)>0, Warehouse), Item, Warehouse), ','))

Re: Set Analysis with Concat

sunny_talwar — Fri, 17 Jun 2016 13:26:00 GMT

Or this:

If(Sum(Qty) > 0, Concat({<Region>}TOTAL <Item> Aggr(If(Sum(Qty) > 0, Only({<Region=,Warehouse={"=sum({<Region=,Warehouse=>} Qty)>0"}>} Warehouse)), Region, Warehouse, Item), ', '))

Re: Set Analysis with Concat

swuehl — Fri, 17 Jun 2016 13:29:55 GMT

If(Sum(Qty) > 0,

Concat({1} TOTAL<Item> Aggr( If(sum({1} Qty)>0, Only({1}Warehouse)), Region, Item, Warehouse), ','))

Re: Set Analysis with Concat

Fri, 17 Jun 2016 13:31:36 GMT

This is closer except now it's hiding the 0 quantity lines...

Re: Set Analysis with Concat

sunny_talwar — Fri, 17 Jun 2016 13:31:39 GMT

You might also need to ignore selection in Region for If(Sum(Qty) > 0 check also...

If(Sum(Qty) > 0, Concat({<Region>}TOTAL <Item> Aggr(If(Sum({<Region>}Qty) > 0, Only({<Region=,Warehouse={"=sum({<Region=,Warehouse=>} Qty)>0"}>} Warehouse)), Region, Warehouse, Item), ', '))

Re: Set Analysis with Concat

sunny_talwar — Fri, 17 Jun 2016 13:33:19 GMT

You want to see 0 qty lines as well?

Concat({<Region>}TOTAL <Item> Aggr(If(Sum({<Region>}Qty) > 0, Only({<Region=,Warehouse={"=sum({<Region=,Warehouse=>} Qty)>0"}>} Warehouse)), Region, Warehouse, Item), ', ')

Re: Set Analysis with Concat

swuehl — Fri, 17 Jun 2016 13:37:25 GMT

Well, I am a little confused what you want to see in which column.

I guess, by now you should have a toolbox of possible expressions and set analysis expressions to chose from, if Sunny's last two suggested expressions didn't already make it.

Re: Set Analysis with Concat

MK_QSL — Fri, 17 Jun 2016 13:39:47 GMT

Remove If(Sum(Qty) > 0 from Stefan's reply...

Concat({<Warehouse,Item >} TOTAL<Item> Aggr( If(sum({<Region=,Warehouse=>} Qty)>0, Warehouse), Item, Warehouse), ',')

Re: Set Analysis with Concat

sunny_talwar — Fri, 17 Jun 2016 13:39:55 GMT

Hahahahaha

We (especially me) went blazing to provide a solution, without full knowing what was needed exactly

Re: Set Analysis with Concat

Fri, 17 Jun 2016 13:49:58 GMT

I tried that. Now the chart doesn't respond to selections.

The problem with all of these solutions, and the same problem I had originally, is that making different selections (on a warehouse or an item) changes the concatenated list. Item E3, for instance, should show "Houston, Warsaw" on all three lines (even on the Paris line even though it's zero stock) and regardless of what's selected.

Re: Set Analysis with Concat

swuehl — Fri, 17 Jun 2016 13:55:10 GMT

Maybe something like

=Aggr(

Concat({1} TOTAL<Item> Aggr( If(sum({1} Qty)>0, Only({1}Warehouse)), Region, Item, Warehouse), ',')

,Region,Item,Warehouse)

Re: Set Analysis with Concat

sasiparupudi1 — Fri, 17 Jun 2016 14:13:23 GMT

Please try the following

Aggr(NODISTINCT Concat({<Region=,Warehouse={"=aggr(sum({<Region=,Warehouse=>} Qty),Warehouse,Item)>0"}>} Warehouse, ','), Item)