4 Replies Latest reply: Apr 26, 2012 10:04 AM by Martijn ter Schegget RSS

    Just for QVW experts.Combinatorial analysis

      Hello everyone,

       

      I´ve this simple table:

       

      ITEM

      A

      B

      C

      D

       

      Then i need to make this...

       

      ITEM_X

      A

      AB

      AC

      AD

      ABC

      ABD

      ACD

      ABCD

      B

      BA

      BC

      BD

      BAC

      BAD

      BACD

       

      And so on..

       

      Tks

        • Re: Just for QVW experts.Combinatorial analysis
          Jonathan Dienst

          Hi

           

          How's this? Please look at the attachment.

           

          Regards

          Jonathan

          • Re: Just for QVW experts.Combinatorial analysis
            Martijn ter Schegget

            Hi Joao,

             

            Built a QVW to generate this; see the attachment. Not really optimized though...

             

            One other word of warning: the number of combinations grows very fast once you have a larger number of items in your original list (a bit over 100.000 for 8 items, nearly 1 million for 9 items, nearly 8,5 million for 10 items)!

             

            Regards,

            Martijn ter Schegget

            The Implementation Group

             

            PS: The script (in case something messes up the attachment):

             

            values:
            LOAD * INLINE [
                value
                A
                B
                C
                D
                E
                F
                G
                H
            ];
            
            tmp_nrofvalues:
            LOAD
                count(value) as nrofvalues
            RESIDENT values;
            
            LET vNrOfValues = peek('nrofvalues');
            
            DROP TABLE tmp_nrofvalues;
            
            combinations:
            LOAD
                null() as combination
            AUTOGENERATE (0);
            
            FOR vOuterLoop = 1 to $(vNrOfValues)
            
                fixed_length_combinations:
                NOCONCATENATE LOAD
                    '' as fixed_length_combi
                AUTOGENERATE (1);
            
                FOR vInnerLoop = 1 to $(vOuterLoop)
                    JOIN (fixed_length_combinations) LOAD
                        value
                    RESIDENT values;
            
                    JOIN (fixed_length_combinations) LOAD
                        fixed_length_combi,
                        value,
                        fixed_length_combi & value as fixed_length_combi_new
                    RESIDENT fixed_length_combinations
                    WHERE index(fixed_length_combi, value) = 0;
            
                    INNER JOIN (fixed_length_combinations) LOAD DISTINCT
                        fixed_length_combi_new
                    RESIDENT fixed_length_combinations;
            
                    DROP FIELDS fixed_length_combi, value FROM fixed_length_combinations;
                    RENAME FIELD fixed_length_combi_new TO fixed_length_combi;
                NEXT vInnerLoop
            
                CONCATENATE (combinations) LOAD
                    fixed_length_combi as combination
                RESIDENT fixed_length_combinations;
            
                DROP TABLE fixed_length_combinations;
            
            NEXT vOuterLoop