Calculate a new column based on a look up value

[EricFreitas]

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

[settu_periasamy · Accepted solution]

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]

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]

[EricFreitas]

Hi Settu.

Thank you very much. It  worked.

Rgds,

Eric

[EricFreitas]

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

Rgds,Eric