12 Replies Latest reply: Jun 12, 2012 3:36 AM by Markus Schnabl RSS

    Check if two certain products are sold together ?

      Hi,

       

      i got a sales table that looks like

       

       

      TransactionIDProductIDQuantityValue
      12-12341001224.99
      12-1234100219.90
      12-12551001113
      12-13441002219.80

       

       

      I have to build a "connection rate" report to show how often certain predefined products are sold together, for example count the transactions where products 1001 and 1002 are sold within the same transactionID.

       

      In addition there is not only one connection rate defined but more than 10, so a hardcoded expression is not going to do it.

       

      The bad thing is, I dont really know how and where to start.

      Perhaps some of you already had a similar challenge and could give me some hints whether or not such an analysis is possible within qlikview

       

      Thanks in advance,

      Max

        • Re: Check if two certain products are sold together ?
          Stefan Wühl

          If I understood your requirements corretly, you could start with a datamodel that creates a duplicate productID field and link this field to your table via transaction id:

           

          Table1:

          LOAD TransactionID,

               ProductID,

               Quantity,

               Value

          FROM

          [http://community.qlik.com/thread/53995?tstart=0]

          (html, codepage is 1252, embedded labels, table is @1);

           

          Table2:

          LOAD TransactionID, ProductID as ProductID2 resident Table1;

           

          Then just create a grid chart with dimensions ProductID and ProductID2 (second could be re-labeled to ProductID if you want), and as expression

          =if(ProductID<>ProductID2,count(TransactionID))

           

          Hope this helps,

          Stefan

            • Re: Check if two certain products are sold together ?

              Hi swuehl,

              thank you for your reply and sorry for not answering in time but i didnt have the access til now

               

              Your solution would work if you want to analyse which products sell together frequently but not if i have predefined market baskets and count how often those specific combinations exist.

               

              I created a new table which looks like:

               

              Basket, ProductId, Criteria1, Criteria2

               

               

               

              BasketNameProductIDCriteria1Criteria2
              A10011
              A10021
              A1007
              1
              B10011
              B1004
              1
              C10051
              C1009
              1

               

               

              and created a pivot chart with Shop on x axis and BasketName on y

               

              I tried to get the number of transaction using set analysis and it worked nicely when just counting how often Criteria1 transactions exist with

              =count($<Criteria1={1}>} distinct TransactionId)

               

              But i got a unsolvable problem with getting the transactions where criteria1 AND criteria2 are sold. I tried it with

              =count($<TransactionID=P({$<Criteria1={1}>}TransactionID) , Criteria2={1}>} distinct TransactionId)

               

              but the problem is that the P() syntax surprisingly seems to ignore the dimensions and so I get transactions where Basket "A" criteria1 is sold together with Basket "C" criteria2, too

              Is there a way to select/count transactions which got at least 1 product from criteria1 and one from criteria2 within the same basket

               

              thx,

              Max

                • Check if two certain products are sold together ?
                  Stefan Wühl

                  Ok, you want to know something like 'Tell me all Transactions where at least one ProductID is either 1001 or 1002 and another ProductID is 1007', is this right?

                   

                  Do you have for every defined ProductID in your Basket table a transaction in your Transaction table?

                   

                  I noticed that e.g. ProductID 1001 is occurring in Basket A as well as in Basket B. In this example ProductID 1001 is assigned to Criteria1. Is it possible that ProductID 1001 could also be assigned to Criteria 2 in another Basket?

                   

                  It would be nice if you could create an post a small sample qvw file that demonstrates your data as closely as possible.

                  With the data provided above, I can't reproduce the issues with transaction where Basket "A" criteria1 is sold together with Basket "C" criteria2.

                   

                  Maybe it's possible to achieve what you want, but I think if you want to create an AND condition for criteria 1 and 2, you probably need to use an intersection of sets, maybe something like

                   

                  =count(

                  {$<

                  TransactionID = P({$<Criteria1={1}>} TransactionID) * P({$<Criteria2={1} >} TransactionID) 

                  >}

                  DISTINCT TransactionID)

                   

                  Regards,

                  Stefan