topic Re: Displaying only Unique combinations in QlikView

Displaying only Unique combinations

Wed, 30 Nov 2011 21:41:36 GMT

Hello All,

I have been trying to create a .qvw to display the unique pairs or combinations or items in a table.

The table looks as follows:

Basket, Item

1,M

1,E

2,E

2,B

3,M

3,B

4,M

4,E

5,M

5,E

5,B

What I have done is loaded the table twice and then inner joined the two tables on basket.

Combos:

Load

basket,

item as item1

inner join

Load

basket,

item as item2

This gives me all of the possible combinations with one issue. I get the M,E combination as well as the E,M combination. So when I put this into a Pivot table I get 2 of the same combination with the same count. I need to exclude the duplicate either in the load or in the chart.

Thanks

Marc

Re: Displaying only Unique combinations

johnw — Wed, 30 Nov 2011 22:07:30 GMT

Maybe this?

INNER JOIN (Combos)

LOAD *

WHERE item1 < item2;

Displaying only Unique combinations

Wed, 30 Nov 2011 22:51:08 GMT

i'm a little confused. Is this a replacement join or another join to be put at the end?

Displaying only Unique combinations

johnw — Wed, 30 Nov 2011 22:57:25 GMT

To be put at the end. You can't compare the items until you've already done the join that creates item2 and puts it in the Combo table. So as a first step, you have to duplicate all the combos. Then you inner join like this to keep only one copy. I haven't thought of a way to do it in just a single step, though perhaps there is one.

Displaying only Unique combinations

Wed, 30 Nov 2011 23:01:42 GMT

there is an issue for some reason. When I create the combos table it is using a resident table. When trying to add an inner join to the Combos table it says that the table can't be found. Is it possible to inner join to a table created from a resident table?

Re: Displaying only Unique combinations

johnw — Wed, 30 Nov 2011 23:04:58 GMT

Yes, it is possible to inner join to any table, including one created from some other resident table. AH! I made a stupid mistake, one I make in my own applications all the time. I forgot to say what we're loading from. I assume that's the problem, and if it's not, it's a big problem that needs to be fixed:

INNER JOIN (Combos)

LOAD *

RESIDENT Combos

WHERE item1 < item2;

Re: Displaying only Unique combinations

Wed, 30 Nov 2011 23:17:30 GMT

To compound that i had the incorrect Case as well doh!! Now my issue is that there is duplication. It is definitely related to my data since on a small test set it works perfect. Thanks John