topic How to create a calculated table at loading time in QlikView

How to create a calculated table at loading time

vksthkkr — Sat, 07 Sep 2013 10:29:42 GMT

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.

Re: How to create a calculated table at loading time

jagan — Sat, 07 Sep 2013 10:52:56 GMT

Hi Vikas,

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

Regards,

Jagan.

Re: How to create a calculated table at loading time

vksthkkr — Sat, 07 Sep 2013 11:18:19 GMT

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.

Re: How to create a calculated table at loading time

MayilVahanan — Sat, 07 Sep 2013 11:27:18 GMT

Can you say the link between two tables in db?

Re: How to create a calculated table at loading time

vksthkkr — Sat, 07 Sep 2013 11:31:20 GMT

Mayil,

Have responded to Jagan on the same question above.

Re: How to create a calculated table at loading time

vksthkkr — Sat, 07 Sep 2013 18:12:38 GMT

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??

Re: How to create a calculated table at loading time

jagan — Tue, 10 Sep 2013 11:03:58 GMT

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.

Re: Re: How to create a calculated table at loading time

vksthkkr — Fri, 20 Sep 2013 13:37:47 GMT

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?

Re: How to create a calculated table at loading time

jonathandienst — Fri, 20 Sep 2013 14:14:59 GMT

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

Re: How to create a calculated table at loading time

vksthkkr — Wed, 25 Sep 2013 07:17:56 GMT

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.

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.

Re: Re: How to create a calculated table at loading time

jonathandienst — Wed, 25 Sep 2013 14:35:23 GMT

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

Re: How to create a calculated table at loading time

vksthkkr — Thu, 26 Sep 2013 05:33:50 GMT

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

Re: How to create a calculated table at loading time

jonathandienst — Fri, 27 Sep 2013 05:55:30 GMT

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

Re: How to create a calculated table at loading time

vksthkkr — Sat, 28 Sep 2013 11:02:02 GMT

Jonathan Dienst

Worked like a charm.

Thanks a ton!!