# New to QlikView

Discussion board where members can get started with QlikView.

New 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

Tags (2)
Labels (2)

• ### Script Logic

1 Solution

Accepted Solutions MVP

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

Hi ,

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

Curr_Temp:
LOAD * INLINE [
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 MVP

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

Hi ,

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

Curr_Temp:
LOAD * INLINE [
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;

New Contributor III

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

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])
New Contributor III

## Re: Calculate a new column based on a look up value New Contributor II

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

Hi Settu.

Thank you very much. It  worked.

Rgds,

Eric

New Contributor II

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

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

Rgds,Eric