topic Re: subquery in measure in App Development

subquery in measure

QlikCommSa — Mon, 26 Apr 2021 12:05:27 GMT

Hi!

Ich have a central stock and a few other stocks which are all supplied by the central stock. In a table for each stock all articles with quantity is listed.

In my app I want to visualize a table where I have the list of articles with the stock the article is available in and the quantity of the article in this stock. In addition I want to have a column where the quantity of the central stock of this article is listed.

Is there a way to select the quantity of the central stock only in a measure of this table? I don't want to select it via subquery in loading script, because it increases the loading time massively and I don't have any option to edit the database.

Example:

Article	Stock	Quantity	Quantity Central Stock
Book A	Berlin	3	10
Book A	London	5	10
Book B	Berlin	10	20
Book A	Central Stock	10	10
Book B	Central Stock	20	20

Thank you in advance!

Re: subquery in measure

Lauri — Mon, 26 Apr 2021 19:11:59 GMT

Did you try Set Analysis?

Sum({<Stock={'Central Stock'}>} Quantity)

Re: subquery in measure

QlikCommSa — Tue, 27 Apr 2021 06:36:56 GMT

Sure, but then only the rows where Stock = Central Stock have nonzero quantities in column "quantity central stock"

Re: subquery in measure

JuanGerardo — Tue, 27 Apr 2021 07:20:46 GMT

Hi @QlikCommSa , what about this?:

Sum({<Stock={'Central Stock'}>} TOTAL <Article> Quantity)

Re: subquery in measure

QlikCommSa — Tue, 27 Apr 2021 07:32:19 GMT

Great, that's what I was looking for. Thank you JuanGerardo!