topic Re: Calculating fields in load script in QlikView

Calculating fields in load script

Anonymous — Fri, 21 Dec 2012 00:39:50 GMT

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.

Re: Calculating fields in load script

Fri, 21 Dec 2012 04:42:38 GMT

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

Re: Calculating fields in load script

Fri, 21 Dec 2012 07:03:07 GMT

Yes, this is right way.

Re: Calculating fields in load script

Anonymous — Fri, 21 Dec 2012 13:06:22 GMT

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?

Re: Calculating fields in load script

Gysbert_Wassenaar — Fri, 21 Dec 2012 13:49:00 GMT

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.

Re: Calculating fields in load script

Anonymous — Fri, 21 Dec 2012 13:53:09 GMT

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?