Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vksthkkr
Contributor III
Contributor III

How to create a calculated table at loading time

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.

13 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
vksthkkr
Contributor III
Contributor III
Author

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.

err1.JPG.jpg

err2.JPG.jpg

I'm guessing this is a minor issue.

But I have about clue on how to correct this

jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
vksthkkr
Contributor III
Contributor III
Author

Jonathan Dienst

Worked like a charm.

Thanks a ton!!