3 Replies Latest reply: Jan 20, 2012 6:26 PM by Karl Pover RSS

    Self Join a Field

      I have a table with two fields, contact ID and event ID.  I'm trying to join the event ID to itself, per contact ID.

      From the example below, I have one contact who has interacted with three events, A,B,C.  I would like to build the event pairs as seen in the bottom table.

       

      Contact IDEvent ID
      1A
      1B
      1C
      Result I'm trying to create:
      Contact IDEvent IDEvent ID
      1AB
      1AC
      1BA
      1BC
      1CA
      1CB
        • Re: Self Join a Field
          Karl Pover

          Here's an example of a left join that is later filtered to remove rows with the same values.

           

          Karl

            • Self Join a Field

              Thanks for your help and the sample, Karl.  Although the example is interesting, it seems designed for a slightly different problem than the one I'm considering.  I'm looking for an QV expression that will accept my two columns of data (contact ID and event ID) and produce all the event pair combinations that a given contact ID has interacted with. 

               

              The data will allow my team to segment our customer base by similarity of attended events.  I think QV could save me hours of off-line time if I could find an answer.

               

              Thanks.

                • Re: Self Join a Field
                  Karl Pover

                  Here's an example with your data.  You would have to handle 2 catalogs for events because one table can't have 2 columns EventID.  If this isn't work you are looking for, please post an example of a report you would use for your analysis to understand it better.

                   

                  Karl