Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
Hi Vikas,
What is the common field between this two tables? Join the two tables instead of creating 3rd table.
Regards,
Jagan.
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?
Mayil,
Have responded to Jagan on the same question above.
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??
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.
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
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:
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.