Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis with Concat

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe something like

=Aggr(

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

,Region,Item,Warehouse)

View solution in original post

21 Replies
sunny_talwar

Do you need this?

Capture.PNG

Expression:

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

MK_QSL
MVP
MVP

Can you give one example what you thing wrong here?

swuehl
MVP
MVP

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 AmericaHoustonA15Houston,Montreal,Paris,Warsaw
North AmericaHoustonB62Houston,Montreal,Paris
North AmericaHoustonE31Houston,Paris,Warsaw
North AmericaHoustonG993Houston,Montreal,Warsaw
North AmericaHoustonZ82Houston,Montreal,Paris,Warsaw
North AmericaMontrealA19Houston,Montreal,Paris,Warsaw
North AmericaMontrealB62Houston,Montreal,Paris
North AmericaMontrealD94Montreal,Warsaw
North AmericaMontrealG991Houston,Montreal,Warsaw
North AmericaMontrealZ82Houston,Montreal,Paris,Warsaw
EuropeParisA14Houston,Montreal,Paris,Warsaw
EuropeParisB62Houston,Montreal,Paris
EuropeParisZ81Houston,Montreal,Paris,Warsaw
EuropeWarsawA112Houston,Montreal,Paris,Warsaw
EuropeWarsawC23Houston,Montreal,Warsaw
EuropeWarsawD94Montreal,Warsaw
EuropeWarsawE35Houston,Paris,Warsaw
EuropeWarsawZ810Houston,Montreal,Paris,Warsaw
Not applicable
Author

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
MVP
MVP

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 AmericaHoustonA15Houston,Montreal,Paris,Warsaw
North AmericaHoustonB62Houston,Montreal,Paris
North AmericaHoustonE31Houston,Warsaw
North AmericaHoustonG993Houston,Montreal
North AmericaHoustonZ82Houston,Montreal,Paris,Warsaw
North AmericaMontrealA19Houston,Montreal,Paris,Warsaw
North AmericaMontrealB62Houston,Montreal,Paris
North AmericaMontrealD94Montreal,Warsaw
North AmericaMontrealG991Houston,Montreal
North AmericaMontrealZ82Houston,Montreal,Paris,Warsaw
EuropeParisA14Houston,Montreal,Paris,Warsaw
EuropeParisB62Houston,Montreal,Paris
EuropeParisZ81Houston,Montreal,Paris,Warsaw
EuropeWarsawA112Houston,Montreal,Paris,Warsaw
EuropeWarsawC23Warsaw
EuropeWarsawD94Montreal,Warsaw
EuropeWarsawE35Houston,Warsaw
EuropeWarsawZ810Houston,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.

Not applicable
Author

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


Capture.PNG

swuehl
MVP
MVP

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

Capture.PNG