Set Analysis with Concat

[#1]

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.

[sunny_talwar]

Do you need this?

Expression:

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

[MK_QSL]

Can you give one example what you thing wrong here?

[swuehl]

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

[#5]

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.

[swuehl]

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.

[#7]

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

[sunny_talwar]

Try this:

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

[swuehl]

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

[sunny_talwar]

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), ', '))