3 Replies Latest reply on Mar 8, 2019 3:14 AM by Nico Heinze

    Generate all possible combinations of values in a column

    Anand Rajakrishnan Active Member

      My source table looks like:

       

      Col_1Col_2
      1F
      2S
      3A

       

      I want to generate a target that has all possible combinations of values from column Col_2 and add a surrogate key. The target should be like:

      SKCombination
      1A
      2F
      3S
      4A,F
      5A,S
      6F,S
      7A,F,S

      Please note that all values in Col_2 are unique. Thanks in advance.

        • 1. Re: Generate all possible combinations of values in a column
          Nico Heinze Guru

          Off the top of my head I don't see any way to perform this combinatorial challenge with any out-of-the-box transformations in PowerCenter. I would use a Java Transformation or a Custom Transformation (in C) to do that. Performance-wise the CT is better, in terms of maintenance the JTX usually is better.

           

          Regards,

          Nico

          1 of 1 people found this helpful
          • 2. Re: Generate all possible combinations of values in a column
            Anand Rajakrishnan Active Member

            Nico,

             

            If I understood you right, I'll use expression and aggregator transformations to collect all values of Col_2 of the source in a single row. Then use Java or Custom transformation. I understand Java transformation. Can you please help me how can I get all combinations using Java?

             

            Thanks,

            Anand

            • 3. Re: Generate all possible combinations of values in a column
              Nico Heinze Guru

              Programming combinatorial challenges is not my specialty, but - without having googled for good approaches - I would do this:

               

              First read the singular values (in this case, F, S, and A) into an array.

               

              Next calculate the number of combinations (that's simply 2 to the power of the number of singular values, in this case 2^3 = 8); then subtract 1 from this number because we don't output the record for value 0 (which would mean none of the singular values is output, i.e. an empty string would be produced).

               

              Now iterate in a "for" loop from 1 to 7. The loop counter variable ís a bit mask, so I would now use another "for" loop within the first "for" loop to iterate from bit 0 to the highest bit of the loop counter. If the respective bit of the loop counter is set, append the singular value with the index "inner loop counter" plus a "," to the output string.

               

              A bit complicated, I know. Here's a bit of program code (not complete):

               

              Let's suppose you already have stored all singular input values into an array like this:

                String[] a = { "F", "S", "A" };

              And that the JTX has an output port outputlist of type String

              Now you do this:

               

              long cntCombinations = (1 << a .length) - 1;

              StringBuilder sb = new StringBuilder [1000];

              for (outer = 1; outer < cntCombinations; outer++)

              {   sb .setLength( 0);

                  for (inner = 0; inner < a .length; inner++)

                      if ( (outer / (1 << (inner - 1))) % 2 == 1)

                      {   sb .append( a [inner];

                          sb .append( ',');

                      }

                  // cut off trailing ",":

                  sb .setLength( sb .getLength() - 1);

                  outputlist = sb .toString();

                  generateRow()

              }

               

              Granted, that's a little simplifed, but the principle should work fine.

               

              Regards,

              Nico

              1 of 1 people found this helpful