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

# Generate all possible combinations of values in a column

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

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

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

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