Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Displaying only Unique combinations

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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;

View solution in original post

6 Replies
johnw
Champion III
Champion III

Maybe this?

INNER JOIN (Combos)

LOAD *

WHERE item1 < item2;

Not applicable
Author

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

johnw
Champion III
Champion III

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.

Not applicable
Author

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?

johnw
Champion III
Champion III

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;

Not applicable
Author

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