How to load 2 different tables and analyse them seperatly in the same document

[#1]

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

[Colin-Albert]

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?

[#3]

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!

[#4]

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

[Anonymous]

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.