Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
vishalj88
New Contributor

Self Join Table Transformation

Hi,

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

Column
a
b
c

Result

Column1Column2
ab
ac
bc

 

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

1 Solution

Accepted Solutions
Partner
Partner

Re: Self Join Table Transformation

If that is the case,

Create another identifier by multiplying both unique rows:

clipboard_image_0.png

clipboard_image_2.png

Thanks and regards,

Arthur Fong

View solution in original post

5 Replies
Partner
Partner

Re: Self Join Table Transformation

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:

clipboard_image_0.png

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 

Partner
Partner

Re: Self Join Table Transformation

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: 

clipboard_image_0.png

Refer attached qvw for reference.

Thanks and regards,

Arthur Fong

 

vishalj88
New Contributor

Re: Self Join Table Transformation

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. 

Partner
Partner

Re: Self Join Table Transformation

If that is the case,

Create another identifier by multiplying both unique rows:

clipboard_image_0.png

clipboard_image_2.png

Thanks and regards,

Arthur Fong

View solution in original post

vishalj88
New Contributor

Re: Self Join Table Transformation

brilliant! Thanks Arthur