Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a table with orders details, with amount in $.
Another table with dates and Euro rates per date.
I would like to calculate the amount in Euro in the load script.
another words:
For each order, calculate the amount in Euro, and add the calcuated field in the script.
Would appreciate your help.
[Currency Rates]:
LOAD Date as RateDate,
USDRate,
EURRate
FROM
xxx.xls]
[Backlog Orders]:
LOAD Date as BacklogDate,
Customer,
Balance,
Balance/EURRate as BalanceEUR
FROM
YYY.XLS
You can use the lookup function in the script:
[Backlog Orders]:
LOAD Date as BacklogDate,
Customer,
Balance,
Balance/ lookup('EURRate','RateDate',Date,'Currency Rates') as BalanceEUR
FROM
YYY.XLS
or use your currency rates as Mapping table:
[Currency Rates]:
Mapping LOAD Date as RateDate,
// USDRate,
EURRate
FROM
xxx.xls]
[Backlog Orders]:
LOAD Date as BacklogDate,
Customer,
Balance,
Balance/ applymap('Currency Rates',Date) as BalanceEUR
FROM
YYY.XLS
You can use the lookup function in the script:
[Backlog Orders]:
LOAD Date as BacklogDate,
Customer,
Balance,
Balance/ lookup('EURRate','RateDate',Date,'Currency Rates') as BalanceEUR
FROM
YYY.XLS
or use your currency rates as Mapping table:
[Currency Rates]:
Mapping LOAD Date as RateDate,
// USDRate,
EURRate
FROM
xxx.xls]
[Backlog Orders]:
LOAD Date as BacklogDate,
Customer,
Balance,
Balance/ applymap('Currency Rates',Date) as BalanceEUR
FROM
YYY.XLS
Great!
I like the first solution as it will apply for having more currencies.
Thank you for your prompt reply!
Thanks!
i'll try it. Although it looks a bit complicated for me to understand,
since i'm just beginning...