Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Currency conversion - Joining tables

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

start_date

company_name

due_total

currency_code

2016-02-01Ovlov54445978
2016-02-01baas5443144
2015-03-01Ovlov65543978
2014-01-01baas6654144

Along with a currency conversion table of the structure:

Yearcurrency_codecurrency_value
20169780.766
2015978

0.801

20161445.767
20151446.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!

Tags (1)
1 Solution

Accepted Solutions
undergrinder
Valued Contributor II

Re: Currency conversion - Joining tables

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.

6 Replies
undergrinder
Valued Contributor II

Re: Currency conversion - Joining tables

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
Valued Contributor II

Re: Currency conversion - Joining tables

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.

Not applicable

Re: Currency conversion - Joining tables

That did it, thank you very much

undergrinder
Valued Contributor II

Re: Currency conversion - Joining tables

undergrinder
Valued Contributor II

Re: Currency conversion - Joining tables

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

G.

tomovangel
Contributor III

Re: Currency conversion - Joining tables

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

Community Browser