Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to connect a table with data in country's currencies with a table with exchange rates, aiming to produce a final table with figures both in local and converted (EUR) values. The tables look similar to this:
Table 1:
Country | Date | Sales (local) |
AT | 01/01/2015 | 111 |
HU | 02/01/2015 | 222 |
DE | 03/01/2015 | 333 |
UK | 04/01/2015 | 444 |
… | … | … |
Table 2:
Date | HUFEUR | GBPEUR |
01/01/2015 | 0.0054 | 1.2245 |
02/01/2015 | 0.0034 | 1.2225 |
03/01/2015 | 0.0028 | 1.2285 |
04/01/2015 | 0.0068 | 1.2215 |
… | … | … |
Obviously, when calculating the figures, the script should take into account the correct exchange rate for the particular country, and also skip countries which already use EUR. The third table (or maybe append to the 1st one) I am aiming to produce should look something like this:
Country | Date | Sales (local) | Sales (eur) |
AT | 01/01/2015 | 111 | 123 |
HU | 02/01/2015 | 222 | 50 |
DE | 03/01/2015 | 333 | 333 |
UK | 04/01/2015 | 444 | 234 |
… | … | … | … |
Try like
Table1:
load
Country,
Date,
[Sales(Local)]
from Source1;
left join
load
Date,
HUFEUR,
GBPEUR
From Surce2;
Now you can create chart using these data.
Hi,
This is a great idea but i don't think it will suit my needs. I would like to have the figures already calculated in the script, as in my case it won't be efficient to have the calculations in the charts.
Hi Georgi, after the code proposed by Pradip you have a table with the exchange for each row (if the date exists in source2).
After that you only need to calculate the exchange:
TableWithExchangeApplied:
LOAD *, [Sales(Local)] * GBPEUR as [Sales(eur)]
Probably you'll need a field in your sales table to know wich exchange to apply (GBPEUR or HUFEUR)