6 Replies Latest reply: Jul 21, 2015 5:56 PM by Stefan Wühl RSS

    cartesian product type problem

    Vishal Jada

      Hi Qlik Community,

       

      I have a table of values like below A, B, C

       

      Column
      A
      B
      C

       

      I need to be able to create the following table that takes the variations of values from 1st table and concatenates them in 1 row.

       

      New Column

      A-B-C

      A-C-B
      B-A-C
      B-C-A
      C-A-B
      C-B-A

       

      I hope this made sense. I presume this would require some type of cartesian join, but i could be wrong. Please advise.

       

      Regards

       

      V

        • Re: cartesian product type problem
          Sunny Talwar

          Try this:

           

          Table:

          LOAD Column as Col1

          FROM

          [https://community.qlik.com/thread/173474]

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

           

          Join(Table)

          LOAD Column as Col2

          FROM

          [https://community.qlik.com/thread/173474]

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

           

          Join(Table)

          LOAD Column as Col3

          FROM

          [https://community.qlik.com/thread/173474]

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

           

          FinalTable:

          NoConcatenate

          LOAD *,

            Col1 & '-' & Col2 & '-' & Col3 as NewColumn

          Resident Table

          Where (Col1 <> Col2) and (Col1 <> Col3) and (Col2 <> Col3);

           

          DROP Table Table;

           

          Output:

           

          Capture.PNG

            • Re: cartesian product type problem
              Vishal Jada

              Thanks Sunny. I figured the solution would look something like yours. However, we are told that the 1st table has 3 values. What if we aren't told that ? How can we dynamically recode the logic and arrive at the same solution?

               

              Thanks in advance.

               

              Regards

              V

                • Re: cartesian product type problem
                  Sunny Talwar

                  Columns can be created, like this, trying to move forward from here:

                   

                  Table:

                  LOAD * Inline [

                  Col1

                  A

                  B

                  C

                  D

                  E

                  F

                  ];

                   

                  CountCol:

                  LOAD Count(Col1) as CountCol

                  Resident Table;

                   

                  LET vCount = Peek('CountCol');

                  DROP Table CountCol;

                   

                  FOR i = 1 to $(vCount)

                   

                    Join(Table)

                    LOAD Col as Col$(i)

                    Inline [

                    Col

                    A

                    B

                    C

                    D

                    E

                    F

                    ];

                   

                  NEXT

                   

                  Feel free to give ideas or make changes to move forward, while I troubleshoot

                    • Re: cartesian product type problem
                      Vishal Jada

                      That helped sunny. Thanks.

                        • Re: cartesian product type problem
                          Sunny Talwar

                          That helped already??? Didn't we have to concatenate them?

                           

                          I was spending time for no reason I guess

                           

                          Well, I am glad you got what you were looking for.

                           

                          Best,

                          Sunny

                            • Re: cartesian product type problem
                              Stefan Wühl

                              Too bad QV only support equi-joins.

                               

                              I started with a joining the original table in a loop, but without removing rows with duplicate column values, the script for permutations of 8 values run approx. 1 hour and started swapping.

                               

                              This version filters rows after each join, so it runs only 1 minute ;-)

                               

                               

                              Origin:
                              LOAD * Inline [
                              Col
                              A
                              B
                              C
                              D
                              E
                              F
                              G
                              H
                              ];
                              
                              CountCol:
                              LOAD Count(Col) as CountCol
                              Resident Origin;
                              
                              LET vCount = Peek('CountCol');
                              DROP Table CountCol;
                              
                              FOR i = 1 to $(vCount)
                              
                                If i = 1 Then
                                Set vVar = 'Table:';
                                ELSE
                                Set vVar = 'Join (Table)';
                                ENDIF
                              
                                $(vVar)
                                LOAD Col as $(i)
                                RESIDENT Origin;
                                 
                                CROSS:
                                CROSSTABLE (Col, Value)
                                LOAD recno() as ID, *
                                RESIDENT Table;
                              
                                TMP:
                                LOAD ID as IDValid
                                WHERE Max =1;
                                LOAD ID,max(Count) as Max
                                GROUP BY ID;
                                LOAD ID, Value, Count(Value) as Count
                                Resident CROSS
                                GROUP BY ID, Value;
                              
                              
                                TMPTABLE:
                                NOCONCATENATE
                                LOAD * Resident Table
                                WHERE EXISTS(IDValid,recno());  
                                 
                              
                                DROP TABLE TMP, Table;
                              
                                Rename Table TMPTABLE to Table;  
                                
                              NEXT
                              
                              
                              DROP TABLE Origin;
                              
                              CROSS:
                              CROSSTABLE (Col, Value)
                              LOAD recno() as ID, *
                              RESIDENT Table;
                              
                              RESULT:
                              LOAD CONCAT(Value, '-', num#(Col) ) as Permut
                              Resident CROSS
                              GROUP BY ID;