Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
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.

1 Solution

Accepted Solutions

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

View solution in original post

13 Replies
MVP & Luminary
MVP & Luminary

Hi Vikas,

What is the common field between this two tables?  Join the two tables instead of creating 3rd table.

Regards,

Jagan.

Contributor III
Contributor III

Hi Jagan,

There is no common field between the 2 tables.

However, the Headings for Products (P1, P2...) are common for both tables. Will that be of any help?

If not, I can modify the structure of the Price table only.

Please guide on how that can be done.

HI

Can you say the link between two tables in db?

Thanks & Regards,
Mayil Vahanan R
Contributor III
Contributor III

Mayil,

Have responded to Jagan on the same question above.

Contributor III
Contributor III

Jagan Mohan, Mayil Vahanan Ramasamy

I am a beginner at this and all the work I am doing is self-thought.

So please bear with me if my questions seem strange.

Is it necessary to have a common field between the sales and price tables?

The data has been loaded as Sales_XX and Price_XX in memory. Is it not possible to just multiply the values that have already been loaded in memory??

MVP & Luminary
MVP & Luminary

Hi,

You must have common field between the two tables, then only you are able to join the tables and multiply the values.

Regards,

Jagan.

Contributor III
Contributor III

I managed to create an indirect link between the order and price tables through the customer.

The new script s follows:

Sales Table:

SQL SELECT

id as Sales_Record_ID,

CustID as Cust_ID,

State as Cust_State,

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,

state as Cust_State

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);

Can this help in getting the necessary results?

Hi

The prices that you need are identified by BrandName, Cust_State and Pricing_Year. You need some way pf idenifying these 3 fields from the fields in [Sales Table]. Without that relationship you have no idea of which sales quantity record to be multiplied by which [Price Record] record.

You might also want to crosstable load both sets of data to decompose the tabular structure in the data to a more convenient database structure. Check the manual for information in crosstable.

Regards

Jonathan

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

Jonathan,

Thanks for the inputs. The cross table re-load greatly helped in streamlining the data.

Here is the Data Model after doing a cross table re-load.

Reload.JPG.jpg

What I am trying to do is to

1. Create a new table called Retailer_Revenue which has:

  • Retailer ID
  • Purchase Year (From the Retailer_Purchase Table)
  • Product-Wise Revenue (There are 30 Products in the Purchase and NRV Table)

2. Revenue is to be calculated by using the Ret_Purchase from the Retailer_Purchase Table, multiple that with NRV from the NRV Table wherever the Ret_Purchase Year = NRV_Year AND Ret_Brand = Product (from the NRV Table)

Similar calculation for another table to get Distributor Revenues.

I am new to QV and have no clue as to how to script this.

Please help.