Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
vishalj88
Contributor II
Contributor II

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

Labels (4)
1 Solution

Accepted Solutions
Arthur_Fong
Partner
Partner

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
Arthur_Fong
Partner
Partner

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 

Arthur_Fong
Partner
Partner

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
Contributor II
Contributor II
Author

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
Partner
Partner

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
Contributor II
Contributor II
Author

brilliant! Thanks Arthur