Use a field from a table as a variable

[AlexO84]

Hi all,

I'm new to Qliksense, and I'm trying to figure this out for several days now... With not much success..

Here is the current situation:

• I have a table with purchase orders in several different currencies (Local currency is ILS):

                    

• I have a table with currency rates to ILS (updates automatically) - The latest rate is marked with FLAG=1:

                  

• I have a text box that shows the total money spent: I calculate it by turning everything to ILS (IEXCHANGE is the conversion to ILS for each purchase), and then turning it to the desired currency (by dividing it by the latest value of the desired currency - XXX) - 

                                        sum ([Unit Price]*[Order Quantity]*IEXCHANGE/XXX)

What I'd like to do, is to have a variable that I can select the desired currency, and have the formula above calculate the total automatically, to look something like this:

EXCHANGE(Item Currency, FLAG=1)

sum ([Unit Price]*[Order Quantity]*IEXCHANGE/EXCHANGE)

In short,

I need to use a field from a table where FLAG=1 and the Item Currency is as I select manually.

Hope I was able to explain myself 🙂

[Or]

To get the flagged value for a specific currency set in a variable vCurrency:

Load * INLINE [
Currency, Rate, Flag
USD, 3.2, 1
USD, 3.2, 0
VND, 0.001, 1
VND, 0.002, 0
];

Alt(Only({<Flag = {1}, Currency = {'$(vCurrency)'} >} Rate),1)

Note - the alt() is only necessary if you don't have the currency's conversion to itself, i.e.. ILS to ILS = 1. If you don't have that, the Only() will return null, so alt() will tell it to pick out 1 instead.

[AlexO84]

Thanks a lot, I'll test it and update 🙏