Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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;