topic Re: Adding calculated field in script in QlikView

Adding calculated field in script

Mon, 11 Feb 2013 13:07:53 GMT

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

Re: Adding calculated field in script

swuehl — Mon, 11 Feb 2013 13:27:01 GMT

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

Re: Adding calculated field in script

Mon, 11 Feb 2013 14:06:39 GMT

Great!

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

Thank you for your prompt reply!

Re: Adding calculated field in script

Mon, 11 Feb 2013 14:45:33 GMT

Thanks!

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

since i'm just beginning...