How to create a calculated table at loading time

[vksthkkr]

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.

[jonathandienst]

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

[vksthkkr]

Jonathan Dienst,

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

[jonathandienst · Accepted solution]

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

[vksthkkr]

Jonathan Dienst

Worked like a charm.

Thanks a ton!!