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.

[jagan]

Hi Vikas,

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

Regards,

Jagan.

[vksthkkr]

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.

[MayilVahanan]

HI

Can you say the link between two tables in db?

[vksthkkr]

Mayil,

Have responded to Jagan on the same question above.

[vksthkkr]

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

[jagan]

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.

[vksthkkr]

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?

[jonathandienst]

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

[vksthkkr]

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.