Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
Maybe this?
INNER JOIN (Combos)
LOAD *
WHERE item1 < item2;
i'm a little confused. Is this a replacement join or another join to be put at the end?
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.
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?
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;
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