Currency conversion - Joining tables

[#1]

Hi! I have a table of data of the structure (lets call it Sales):

start_date	company_name	due_total	currency_code
2016-02-01	Ovlov	54445	978
2016-02-01	baas	5443	144
2015-03-01	Ovlov	65543	978
2014-01-01	baas	6654	144

Along with a currency conversion table of the structure:

Year	currency_code	currency_value
2016	978	0.766
2015	978	0.801
2016	144	5.767
2015	144	6.5

What I want to do is to add a field in the Sales (due_total_myCurrency) that takes the due_total and multiplies it with the currency_value of the same currency_code AND year. The currency conversion table is created with the crosstable prefix.

Any Ideas would be greatly appreciated!

[undergrinder · Accepted solution]

Hi Ludvig,

Try this:

tmp_Sales:

Load * from [Sales_input];

left join(tmp_sales)

Load * from [Currency_conversion_input];

Sales:

Noconcatenate

Load *, due_total * currency_value as due_total_myCurrency Resident tmp_Sales;

drop table tmp_Sales;

G.

[undergrinder]

I think you can do the conversion at the UI as well.

The tables are in associated through currency_code, so you can simply create a master measure for that.

G.

[#4]

That did it, thank you very much

[undergrinder]

[undergrinder]

Please mark the appropriate answer as correct/helpful. Thanks!

G.

[tomovangel]

Hello, How will this master measure look, because i got the same structure, 1 table with sales, and 1 table with currency rates, but my client is selling in over 10 currencies.

So how will this master measure look like?

Thanks in advance,

-Angel