Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
If that is the case,
Create another identifier by multiplying both unique rows:
Thanks and regards,
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
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
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.
If that is the case,
Create another identifier by multiplying both unique rows:
Thanks and regards,
Arthur Fong