Calculating fields in load script

[Anonymous]

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.

[#2]

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

[#3]

Yes, this is right way.

[Anonymous]

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?

[Gysbert_Wassenaar]

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.

[Anonymous]

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?