Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
Ok so I'm a novice at QV, so you may have to explain the long way
I have 2 Excel tables: Sales and Quotes
I have all the sales imported into 1 QV document and have various stats and dashboard reports depending on departments etc.
What I would like to do is add another sheet specifically for Quotes so that I can flick between quotes and sales in the same document.
However not all Quotes will become Sales and some Sales are done via a non-quote process. Some data is quote specific where as other data is available in both the quote and sale table e.g. Customer
How do I pull the data in so that I can run the 2 reports along side each other?
Can I also do a comparison on the 2? e.g. conversion rates (Quotes to Sales by month or by sales person etc.)
Thanks for any help you can give on this
Leah
I would concatenate the Sales & Quotes into a single table.
Add an addition field perhaps "SalesType" which identified the record as a Sales or Quote.
Does your sales data have a field that references the quote number?
Hi Leah,
You can try with a Qualify before the load sentence, what this do is concat the table name to the field names.
See the next example
QUALIFY *;
UNQUALIFY Customer;
Sales:
LOAD * INLINE [
Customer, Sales, OtherField
1, a, x
2, b, y
3, c, z
];
Quote:
LOAD * INLINE [
Customer, Quote, OtherField
1, h, k
2, j, l
3, n, m
];
UNQUALIFY*;
you will get a model like this. I use in line loads, but you can replace it for your data source.
Hope it helps!
Regards!
Not sure if it would work how I need as my 2 data extracts have different columns and different data and need to be analysed differently, If I was to concat it first would it only pull through the columns that match? (all of the ones that do match I have ensured are titled the same)
Here are my data fields, Some match and some don't,
Quote Data | Sales Data |
Brand | RegNo |
Department | Sale Date |
RegNo | Manufacturer |
Manufacturer | Model Description |
Model Description | Colour |
Colour | Department |
Sale Mileage | Brand |
Customer | Early Termination? |
Hirer | Total Sales amount |
ContractTermDate | Sale Amount |
Last Quoted Date | RFL |
AGE | Warranty Sales |
No of Quotes | Net Sales price |
CAP | Sale amount + warranty |
CAP % | Sale Mileage |
Quote Year | Desirable Specification |
QTR of Quote | Priced as an Exception |
Quote Month | Desirable Colour |
Mileage >100k | CAR/LCV |
QuotedAmount | VATStatus |
Total Sales amount | CAP |
RFL | CAP % |
Warranty Sales | Net CAP % |
Warranty Type | Sale Year |
Sale Date | QTR of Sale |
CAR/LCV | Sale Month |
VATStatus | Mileage >100k |
Desirable Specification | Warranty Type |
Priced as an Exception | ContractTermDate |
Buyer profile | AGE |
Buyer | No of Quotes |
Sales Person | Last Quoted Date |
Quote | Age |
statusid | Sales Person |
Customer | |
Buyer | |
Buyer profile | |
statusid |
Concatonate would create a table with all distinct column names - meaning it would not eliminate columns. You just want to make sure that any columns that should be considered the same are named exactly the same (case counts).
If your tables have many rows, I would consider creating placeholders in each table for columns that don't have a match in the other.
TableA:
Null() as A,
Null() as B,
X, Y, Z
TableB:
A,B,X,Y, Null() as Z
Concatenated Table:
A,B,X,Y,Z
This is particularly important in a multi-tier load strategy where your first tier creates QVD and subsequent tiers consume them. You will want matching columns to maintain an optimized load.
If your tables do not have too many rows, this may not be an issue, though with a concatenated facts table, I would assume it would eventually require this.