topic Re: Calculate a new column based on a look up value in QlikView

Calculate a new column based on a look up value

EricFreitas — Sat, 16 Nov 2024 02:32:30 GMT

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:
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

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

settu_periasamy — Thu, 12 Sep 2019 04:59:57 GMT

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;

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

sugathirajkumar — Thu, 12 Sep 2019 07:19:47 GMT

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

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

sugathirajkumar — Thu, 12 Sep 2019 07:20:40 GMT

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

EricFreitas — Thu, 12 Sep 2019 18:27:28 GMT

Hi Settu.

Thank you very much. It worked.

Rgds,

Eric

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

EricFreitas — Thu, 12 Sep 2019 18:28:51 GMT

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

Rgds,Eric