Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Re: Displaying only Unique combinations

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;

6 Replies
MVP
MVP

Re: Displaying only Unique combinations

Maybe this?

INNER JOIN (Combos)

LOAD *

WHERE item1 < item2;

Not applicable

Displaying only Unique combinations

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

MVP
MVP

Displaying only Unique combinations

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

Displaying only Unique combinations

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?

MVP
MVP

Re: Displaying only Unique combinations

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

Re: Displaying only Unique combinations

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

Community Browser