topic Re: Self Join Table Transformation in QlikView

Self Join Table Transformation

vishalj88 — Sat, 16 Nov 2024 20:09:00 GMT

Hi,

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

Column

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

Re: Self Join Table Transformation

Arthur_Fong — Wed, 04 Sep 2019 01:15:12 GMT

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

Re: Self Join Table Transformation

Arthur_Fong — Wed, 04 Sep 2019 04:42:09 GMT

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

Re: Self Join Table Transformation

vishalj88 — Wed, 04 Sep 2019 13:12:53 GMT

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.

Re: Self Join Table Transformation

Arthur_Fong — Wed, 04 Sep 2019 13:26:56 GMT

If that is the case,

Create another identifier by multiplying both unique rows:

Thanks and regards,

Arthur Fong

Re: Self Join Table Transformation

vishalj88 — Wed, 04 Sep 2019 14:07:07 GMT

brilliant! Thanks Arthur