4 Replies Latest reply: Oct 29, 2014 3:26 PM by Steve Taylor RSS

    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

        • Re: How to load 2 different tables and analyse them seperatly in the same document
          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?

          • 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!

            • 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
                • Re: How to load 2 different tables and analyse them seperatly in the same document
                  Steve Taylor

                  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.