Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
EricFreitas
Contributor II
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
settu_periasamy
Master III
Master III

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;

 

 

 

View solution in original post

5 Replies
settu_periasamy
Master III
Master III

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;

 

 

 

View solution in original post

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

clipboard_image_0.png

EricFreitas
Contributor II
Contributor II
Author

Hi Settu.

Thank you very much. It  worked.

Rgds,

Eric

EricFreitas
Contributor II
Contributor II
Author

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

Rgds,Eric