# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming Aug. 9: New Simplified Authoring for Qlik Sense SaaS – For Details, CLICK HERE
cancel
Showing results for
Did you mean:
Contributor II

## Calculate a new column based on a look up value

Hi Everyone,

I'm stuck with this task i gotta do in order to get my sales amount calculated in multiple currencies;

I've got a QVD with a Standard Price Rate for each of Currency Code listed.

Still, i need to create other columns with a conversion Rate for each currency row as well .

To do so, if i'm calculating the USD Rate for example, i need to look up the "Standard Price Rate" when Currency Code = USD and divide this value by the other Standard Prices per each currency/row (as in the example table):

 Currency Type Currency Code Standard Price Rate USD Rate BRL Rate Brazil Reais BRL 2.2500 USD Standard Price Rate / BRL - Standard Price Rate BRL Standard Price Rate / BRL - Standard Price Rate Chile Pesos CLP 0.0132 USD Standard Price Rate / CLP - Standard Price Rate BRL Standard Price Rate / CLP - Standard Price Rate Colombian Peso COP 0.0030 so on so on Mexico Pesos MXN 0.4647 so on so on United States Dollars USD 9.1232 so on so on

I've tried the following, but it only brought a value to the  USD - Currency Code row - the others got null results;

""Currency:
CurrencyCode ,
[Standard Price Rate],

IF(CurrencyCode = 'USD', [Standard Price Rate] , [Standard Price Rate]/ [Standard Price Rate]) as RateUSD

FROM
[Currency.QVD] (qvd);""

I would be really grateful if someone could shed some light on this for me 🙂

Thank you guys very much.

Eric

Labels (2)

• ### Script Logic

1 Solution

Accepted Solutions
Master III

Hi ,

Have you got a solution? if not, may be try like this

Curr_Temp:
Currency Type, Currency Code, Standard Price Rate
Brazil Reais, BRL, 2.25
Chile Pesos, CLP, 0.0132
Colombian Peso, COP, 0.003
Mexico Pesos, MXN, 0.4647
United States Dollars, USD, 9.1232
];

Join (Curr_Temp)
LOAD [Standard Price Rate] as temp_USD Resident Curr_Temp Where [Currency Code] = 'USD'; //Join the Currency value.

Join (Curr_Temp)
LOAD [Standard Price Rate] as temp_BRL Resident Curr_Temp Where [Currency Code] = 'BRL'; //Join the Currency value.

NoConcatenate

Currency:
IF([Currency Code] = 'USD', [Standard Price Rate] , temp_USD/ [Standard Price Rate]) as USDRate,
IF([Currency Code] = 'BRL', [Standard Price Rate] , temp_BRL/ [Standard Price Rate]) as BRLRate
Resident Curr_Temp;

DROP Table Curr_Temp;
DROP Fields temp_USD,temp_BRL;

5 Replies
Master III

Hi ,

Have you got a solution? if not, may be try like this

Curr_Temp:
Currency Type, Currency Code, Standard Price Rate
Brazil Reais, BRL, 2.25
Chile Pesos, CLP, 0.0132
Colombian Peso, COP, 0.003
Mexico Pesos, MXN, 0.4647
United States Dollars, USD, 9.1232
];

Join (Curr_Temp)
LOAD [Standard Price Rate] as temp_USD Resident Curr_Temp Where [Currency Code] = 'USD'; //Join the Currency value.

Join (Curr_Temp)
LOAD [Standard Price Rate] as temp_BRL Resident Curr_Temp Where [Currency Code] = 'BRL'; //Join the Currency value.

NoConcatenate

Currency:
IF([Currency Code] = 'USD', [Standard Price Rate] , temp_USD/ [Standard Price Rate]) as USDRate,
IF([Currency Code] = 'BRL', [Standard Price Rate] , temp_BRL/ [Standard Price Rate]) as BRLRate
Resident Curr_Temp;

DROP Table Curr_Temp;
DROP Fields temp_USD,temp_BRL;

Creator
USD : (sum({<[Currency Code]={'USD'}>}total [Standard Price Rate]))/ sum([Standard Price Rate]) BRL: (sum({<[Currency Code]={'BRL'}>}total [Standard Price Rate]))/ sum([Standard Price Rate])
Creator

Contributor II
Author

Hi Settu.

Thank you very much. It  worked.

Rgds,

Eric

Contributor II
Author

Thanks Kumar... it's an alternative to chart expressions.

Rgds,Eric