Left Join Resident load and combining fields from both tables

[fredrik_olsson]

Hi,

I have to different tables I want to combine and when I left join InvoiceItems table I want to multiple InvoicePrice & InvoicePriceFactor if InvoicePriceFactor exists for that InvoiceID.

Any help is appreciated! 

InvoiceItems:

InvoiceID	InvoicePrice	InvoiceCost
1001	10	7
1002	12	4
1003	4	3

InvoiceMapping

InvoiceID	InvoicePriceFactor	InvoiceCostFactor
1001	0,7	1
1003	0,3	0

InvoiceItems:
LOAD
InvoiceID
InvoicePrice
InvoiceCost;

InvoiceMapping:
LOAD
InvoiceID,
InvoicePriceFactor,
InvoiceCostFactor;

LEFT JOIN (InvoiceItems)
LOAD IF (InvoicePriceFactor <> '', InvoicePriceFactor *InvoicePrice) as InvoicePrice RESIDENT InvoiceMarginMapping;
DROP TABLE InvoiceMapping;

[wbmuller · Accepted solution]

Hi there

try this:

InvoiceItems_Temp:
LOAD
InvoiceID,
InvoicePrice,
InvoiceCost
From (Your Data Source);

LEFT JOIN (InvoiceItems_Temp)
LOAD
InvoiceID,
InvoicePriceFactor,
InvoiceCostFactor;
From (Your Data Source);

InvoiceItems:
LOAD
InvoiceID,
InvoicePriceFactor * InvoicePrice as InvoicePrice,
InvoiceCost,
InvoicePriceFactor,
InvoiceCostFactor
Resident InvoiceItems_Temp;

DROP TABLE InvoiceItems_Temp;