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.

mhouston00
New Contributor III

Calculating fields in load script

I have loaded a table that looks like this...

Master_General:

LOAD

[Employee ID] as EMP_ID,

[Annual Salary Amount] as EMP_Salary

[CURRENCY KEY] as KEY_Currency,

         

FROM

(ooxml, embedded labels, table is General);

and another loaded table that looks like this...

Exchange_Rates:

LOAD

[CURRENCY KEY] as KEY_Currency,

[EXCHANGE RATE] as Exchange_Rate

FROM

(ooxml, embedded labels, table is Sheet1);

I want to calculate a field in the load script like EMP_Salary * Exchange_Rate.  But I don't know the proper syntax.

Do I use an INLINE load?  Do I need to use RESIDENT?  Do I need to create and then drop a temporary table? Do I need to have both tables load before I can complete these calculations?  Could someone please provide a simple example that illustrates and explains these things.  I can't figure them out from other posts.  Thanks.

5 Replies
Not applicable

Re: Calculating fields in load script

Hi,

do this...

load exchange rates table as mapping load

Exchange_Rates:
Mapping LOAD
[CURRENCY KEY] as KEY_Currency,
[EXCHANGE RATE] as Exchange_Rate

FROM

(ooxml, embedded labels, table is Sheet1);

Then

Master_General:
LOAD
[Employee ID] as EMP_ID,
[Annual Salary Amount] as EMP_Salary
[CURRENCY KEY] as KEY_Currency,

Applymap('Exchange_Rates',[CURRENCY KEY]) As Exchange_Rate,

[Annual Salary Amount] * Applymap('Exchange_Rates',[CURRENCY KEY]) As Atcual_Salary
         
FROM

(ooxml, embedded labels, table is General);

it works....

Niranjan M

Not applicable

Re: Calculating fields in load script

Yes, this is right way.

mhouston00
New Contributor III

Re: Calculating fields in load script

Thanks for the reply.  This helps, but when I use a mapping load it drops the table automatically after script execution.  How can I accomplish the same thing if I have a larger table than Exchange_Rates and I don't want it dropped?

MVP & Luminary
MVP & Luminary

Re: Calculating fields in load script

Just load the table as a regular table too. As long as your master_general table and your exchange_rates table both have a field with the same name, e.g. KEY_Currency the tables will be associated.


talk is cheap, supply exceeds demand
mhouston00
New Contributor III

Re: Calculating fields in load script

Do mean load Exchange_Rates twice--once as a mapping load and once as a normal load?  If not, can you provide an example with full syntax to show how I could calculate a field in the script without using mapping loads?

Community Browser