Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

EricFreitas
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 TypeCurrency CodeStandard Price RateUSD RateBRL Rate
Brazil ReaisBRL2.2500USD Standard Price Rate / BRL - Standard Price RateBRL Standard Price Rate / BRL - Standard Price Rate
Chile PesosCLP0.0132USD Standard Price Rate / CLP - Standard Price RateBRL Standard Price Rate / CLP - Standard Price Rate
Colombian PesoCOP0.0030so onso on
Mexico PesosMXN0.4647so onso on
United States DollarsUSD9.1232so onso on

 

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

""Currency:
LOAD
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)
1 Solution

Accepted Solutions

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:
LOAD *,
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

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:
LOAD *,
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;

 

 

 

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

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

clipboard_image_0.png

EricFreitas
New Contributor II

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

Hi Settu.

Thank you very much. It  worked.

Rgds,

Eric

EricFreitas
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