topic Re: Set Analysis where one field equals another field in QlikView

Set Analysis where one field equals another field

Tue, 19 Aug 2014 14:25:28 GMT

I am trying to get a sum of values where two ID fields from different tables have the same value.

For example, I can get a valid value if I hardcode an ID like "MTG2" below:

sum(total {$<ProdID = {"MTG2"}>} Est_Cost)

But what I want is to get the sum for that row where ProdID equals another field called AltProdID (in a different table, but with the same values as ProdID).

The closest I found in the help documentation was something like this:

sum(total {$<ProdID = {$(#=Only(AltProdID))}>} Est_Cost)

But that does not seem to work and just returns zero.

Re: Set Analysis where one field equals another field

syukyo_zhu — Tue, 19 Aug 2014 14:41:52 GMT

Hi,

Do you get always one value from the field AltProdID or you can get several values.

if juste one value, you can use a variable, if multi value, your can use function "p" like {$<ProdID = p(AltProdID)}>}

Re: Set Analysis where one field equals another field

Tue, 19 Aug 2014 14:55:35 GMT

There can be multiple values. I tried the 'p' function and now it seems to sum all possible values, and not just those where the 2 fields are equal.

Re: Set Analysis where one field equals another field

Tue, 19 Aug 2014 14:57:10 GMT

Can you explain what you meant by using a variable for a single value?

Re: Set Analysis where one field equals another field

syukyo_zhu — Tue, 19 Aug 2014 15:15:26 GMT

i mean that you use eachtime juste a single value of your field in your sum.

why don't you try to join these two tables?

Re: Set Analysis where one field equals another field

Tue, 19 Aug 2014 15:28:44 GMT

This link below will help you to generate the set analysis that you want:

Set Analysis Wizard for QlikView | qlikblog.at