Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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

4 Replies

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

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?

Not applicable

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

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.

model.png

Hope it helps!

Regards!

Not applicable

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

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 DataSales Data
BrandRegNo
DepartmentSale Date
RegNoManufacturer
ManufacturerModel Description
Model DescriptionColour
ColourDepartment
Sale MileageBrand
CustomerEarly Termination?
HirerTotal Sales amount
ContractTermDateSale Amount
Last Quoted DateRFL
AGEWarranty Sales
No of QuotesNet Sales price
CAPSale amount + warranty
CAP %Sale Mileage
Quote YearDesirable Specification
QTR of QuotePriced as an Exception
Quote MonthDesirable Colour
Mileage >100kCAR/LCV
QuotedAmountVATStatus
Total Sales amountCAP
RFLCAP %
Warranty SalesNet CAP %
Warranty TypeSale Year
Sale DateQTR of Sale
CAR/LCVSale Month
VATStatusMileage >100k
Desirable SpecificationWarranty Type
Priced as an ExceptionContractTermDate
Buyer profileAGE
BuyerNo of Quotes
Sales PersonLast Quoted Date
QuoteAge
statusidSales Person
Customer
Buyer
Buyer profile
statusid
stevenrtaylor
Contributor II

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

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.

Community Browser