Set Analysis where one field equals another field

[#1]

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.

[syukyo_zhu]

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)}>}

[#3]

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.

[#4]

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

[syukyo_zhu]

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?

[#6]

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

Set Analysis Wizard for QlikView | qlikblog.at