Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Adding calculated field in script

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Adding calculated field in script

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 Replies
MVP
MVP

Re: Adding calculated field in script

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

Not applicable

Re: Adding calculated field in script

Great!

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

Thank you for your prompt reply!

Not applicable

Re: Adding calculated field in script

Thanks!

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

since i'm just beginning...