topic Re: Mapping currency exchange in QlikView

Mapping currency exchange

Mon, 26 Oct 2015 19:13:07 GMT

Hello,

I was wondering if someone can help me.

I currently have a data base, with information about different purchases.

Heres an exmaple of the relevant information.

ID	Date	Amount	Currency
1	01/01/2015	120	CAD
2	05/01/2015	500	EUR
3	06/01/2015	336	SGD
4	02/02/2015	1587	EUR
5	20/01/2015	542	GBP
6	31/01/2015	24	CAD
7	05/02/2015	126	GBP

Besides i have a data base, that has the currency rate per month. It turn everything to dollars. Here is and example:

Currency1	Currency2	Date	Exchange rate
CAD	USD	01/2015	1.243
EUR	USD	01/2015	0.913
CAD	USD	02/2015	1.256
EUR	USD	02/2015	0.911

Here i just showed an example, but both data bases are bigger, and the point is i need to report all my results on USD.

Si i was wondering how i can create an additional field on my first db, so i can add the exchange rate for that purchase.

I need to map this using the date (that is only by month) and the currency.

Is there a way to do this??

I cant just add em and use a sync table because of other elements on the application.

Im looking for a solution inside the loading code.

thanks,

Mon, 26 Oct 2015 19:32:05 GMT

Try this:

Temp:

LOAD ID,

Date,

Amount,

Currency

FROM

Table1;

INNER JOIN (Temp)

LOAD

Currency1 AS Currency,

Date,

[Exchange rate]

FROM

Table2;

Regards!

Marcos Freire

swuehl — Mon, 26 Oct 2015 19:34:21 GMT

You can create a field in your first table to repesent the month, with the same value format as in the second table, maybe like

FACT:
LOAD *,

Right(Date, 7) as Month

RESIDENT YourFirstTable;

LEFT JOIN (FACT)

LOAD Currency1 as Currency,

Date as Month,

[Exchange Rate]

Resident YourExchangeRateTable;

DROP FIELD Month;