Sum values in two tables

[#1]

Hi,

I have tried to do this on my own but keep coming across errors and problems:

I have 2 tables relating to a number of invoices.....

one SQL table - called DataSet: (contains fields LineAmount and CurrencyCode) - The invoice table.

one XL table - called FXRates: (contains fields CurrencyCode and FXRate) - The FX table.

In my Load Script I would like to create a new field for each Invoice called DollarAmount that is the product of LineAmount * FXRate based on the correct CurrencyCode

Any help would be greatly appreciated, I've tried Joins and Speedmaps, but to no avail!

Thanks,

Michael

[ipauldur]

Hi,

You can do the left join of excel table with SQl table using currency code field.

Then, you can derive the ne w field.There is no difficulties...

What error you are getting?

Regards,

Durai.

[#3]

There sometimes can be a issue with the single left join so when loading the data to create a temp table. Then reload this resident table multiplying LineAmount*FXRate. Drop the TempTable

Inv_Det_temp:

LOAD

Invoice_no,

Product,

LineAmount,

CurrencyCode;

SQL SELECT *

FROM inv.detail;

Left join(Inv_Det)

LOAD

CurrencyCode,

FXRate;

SQL SELECT *

FROM currency.detail;

Inv_Det:

LOAD

Invoice_no,

Product,

Line_Amt,

CurrencyCode,

FXRate,

LineAmount* FXRate as ActualAmount

RESIDENT Inv_Det_temp;

Drop table Inv_Det_temp

[#4 · Accepted solution]

There sometimes can be a issue with the single left join so when loading the data to create a temp table. Then reload this resident table multiplying LineAmount*FXRate. Drop the TempTable

Inv_Det_temp:

LOAD

Invoice_no,

Product,

LineAmount,

CurrencyCode;

SQL SELECT *

FROM inv.detail;

Left join(Inv_Det)

LOAD

CurrencyCode,

FXRate;

SQL SELECT *

FROM currency.detail;

Inv_Det:

LOAD

Invoice_no,

Product,

Line_Amt,

CurrencyCode,

FXRate,

LineAmount* FXRate as ActualAmount

RESIDENT Inv_Det_temp;

Drop table Inv_Det_temp

[#5]

Excellent, thanks Carney!