Self Join Table Transformation

[vishalj88]

Hi,

I'm looking to transform the below table such that i obtain the resulting table

Column

a

b

c

Result

Column1	Column2
a	b
a	c
b	c

I realize i have to perform a cartesian product of sorts, but i don't want to count aa, bb, cc. Also i don't want to count ba because it is the same as ab. In other, if original table column has n values, resulting table should contain (n)(n-1)/2 rows. 

How can we do this using QlikView? Any suggestions are welcome. 

Thanks

Vishal

[Arthur_Fong]

My way of doing this will be using ord() function to capture the number of each character.

Summing these 2 columns will get the unique key for each rows:

eg: A(97) + B(98) is the same with B(98) + A(97)

Then, sort these rows according to the Unique Keys.

Finally, apply filter to grab Unique Keys that is not equal to peek(Unique Keys).

Refer attached qvw for reference.

Thanks and regards,

Arthur Fong 

[Arthur_Fong]

If you are handling strings rather than char type column, ord() function doesn't work well.

You need to do a mapping load to get the numeric row count of each distinct records as the Key: 

Refer attached qvw for reference.

Thanks and regards,

Arthur Fong

[vishalj88]

Thanks Arthur for your 2 suggestions. Both are pretty close. However the second solution doesn't work when we have more than 3 distinct values. For instance, in the attached with 6 values you can get to column sum of 8 by multiple ways: 2+6, 3+5. This will incorrectly remove more records than it should. But i think we're on the correct track. Other suggestion are welcome. 

[Arthur_Fong · Accepted solution]

If that is the case,

Create another identifier by multiplying both unique rows:

Thanks and regards,

Arthur Fong

[vishalj88]

brilliant! Thanks Arthur