Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table that has Customer IDs and Product Quantities ordered and another table with Product Prices.
I am displaying here, the load script of the 2 tables for a clear understanding
Sales Table:
SQL SELECT
id as Sales_Record_ID,
CustID as Cust_ID,
P1 as SalesQty_P1,
P2 as SalesQty_P2,
P3 as SalesQty_P3,
P4 as SalesQty_P4,
.
.
.
.
FROM "XXXX".dbo."YYYY" ;
Price Table:
LOAD Product as BrandName,
Year as Pricing_Year,
P1 as Price_P1,
P2 as Price_P2,
P3 as Price_P3,
P4 as Price_P4,
.
.
.
.
FROM
Price.xlsx
(ooxml, embedded labels, table is Sheet1);
Question:
I want to create a new table (on loading time) that reads these 2 tables and creates a 3rd table called Revenue. The Revenue Table should have Customer ID (Cust_ID) and Revenue for each product in a separate column, (SalesQty_P1 * Price_P1, SalesQty_P2 * Price_P2, and so on...)
Please help out with this.
Hi
This should do what you need:
// Get the NRV field into Retailer_Purchase
Inner Join (Retailer_Purchase)
LOAD NRV_Year As Ret_Purchase_Year
Product As Ret_Brand
NRV As Ret_NRV
Resident NRV;
// Calculate Revenue into new Retailer table
Retailer_Purchase2:
NoConcatenate
LOAD
*,
Ret_Purchase * Ret_NRV As Revenue
Resident Retailer_Purchase;
DROP Table Retailer_Purchase;
DROP Field Ret_NRV; // Assuming field no longer needed
Regards
Jonathan
Thanks for the script.
I appended the code you gave as-is to the bottom of my load script.
This seems to throw up 2 errors.
I'm guessing this is a minor issue.
But I have about clue on how to correct this
Hi
Always syntax check stuff you get from the forum . Fix the missing commas:
// Get the NRV field into Retailer_Purchase
Inner Join (Retailer_Purchase)
LOAD NRV_Year As Ret_Purchase_Year,
Product As Ret_Brand,
NRV As Ret_NRV
Resident NRV;
// Calculate Revenue into new Retailer table
Retailer_Purchase2:
NoConcatenate
LOAD
*,
Ret_Purchase * Ret_NRV As Revenue
Resident Retailer_Purchase;
DROP Table Retailer_Purchase;
DROP Field Ret_NRV; // Assuming field no longer needed
Jonathan