Adding calculated field in script

[#1]

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

[swuehl · Accepted solution]

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

[#3]

Great!

I like the first solution as it will apply for having more currencies.

Thank you for your prompt reply!

[#4]

Thanks!

i'll try it. Although it looks a bit complicated for me to understand,

since i'm just beginning...