How to avoid/remove different permutations of the same combination?

[jensmunnichs]

Hey guys, hope you're having a great day.

I recently started using Qlikview, so I'm still new to most of this stuff, but I've already learnt a lot from these forums!

I've been experimenting with some sample data that I think came with the Qlikview installation, an Excel file called Sales Orders.xls (attached). What I want to do is find out what combination of 2 products gets sold together most often. Attached you will find my Qlikview file to see how far I've gotten so far. I'm pretty close, in fact, from the table I've made I can already tell which combinations are the most popular. Problem is, there is currently a row for both product combination A+B and combination B+A. I'm wondering how I would be able to make it so that only one of these permutations exists in my table.

To clarify what I mean:

My current table, sorted by number of occurences descending:

Product 1	Product 2	# of occurences
A	B	200
B	A	200
C	D	110
D	C	110
A	D	90
D	A	90

And so on. So basically, for every combination in my table, there are 2 rows that essentially tell me the same thing.

What I'm looking for:

Product 1	Product 2	# of occurences
A	B	200
C	D	110
A	D	90

What's the best way to solve this? I'm not too bothered whether I have to load the data differently or if I can solve this on the presentation side of things, by removing every 'duplicate' row from the table. I also don't really care which of the 2 permutations stays, if that makes it any easier.

Thanks in advance! Let me know if you need any more information to solve this. Also I apologise if this is a very newbie question, but I've been Googling for a bit and I couldn't find any answers anywhere.

[trdandamudi]

One way is as below:

Data:
Load *
Where SelectFilter = 'Y';
Load *,
IF(Product2 = previous(Product1) and Product1 = previous(Product2),'N','Y') as SelectFilter;
Load * Inline [
Product1,Product2,#ofoccurences
A, B, 200
B, A, 200
C, D, 110
D, C, 110
A, D, 90
D, A, 90
];

[marcus_sommer]

Not tested yet but I could imagine that it worked:

load distinct

     rangeminstring(Product1, Product2) as Product1,

     rangemaxstring(Product1, Product2) as Product2,

     [# of occurences]

from Source;

- Marcus

[MarcoWedel · Accepted solution]

Hi,

maybe one solution could be:

tabSalesOrders:
LOAD Customer,
    [Sales Order ID],
    ShipDate,
    Product,
    Sales,
    Quantity
FROM [https://community.qlik.com/servlet/JiveServlet/download/1551264-339395/Sales%20Orders.xls] (biff, embedded labels, table is [Sales Orders$]);

tabProdComb:
LOAD Distinct
    [Sales Order ID],
    Product as Product1
Resident tabSalesOrders;
Join
LOAD [Sales Order ID],
    Product1 as Product2
Resident tabProdComb;
Right Join
LOAD Distinct
    Product1,
    Product2
Resident tabProdComb
Where Product1 precedes Product2;

hope this helps

regards

Marco

[jensmunnichs]

I apologise, I think I made an error in explaining my situation. The table in my OP is not my source data or my loaded data, it's a straight table I made, so # of occurences is not a field in my data. Am I right in thinking that that means your solution wouldn't work for me unless I count the number of occurences per combination beforehand?

Either way, thanks for your comment. I think Marco already found a solution for me though

[jensmunnichs]

I apologise, I think I made an error in explaining my situation. The table in my OP is not my source data or my loaded data, it's a straight table I made, so # of occurences is not a field in my data. Am I right in thinking that that means your solution wouldn't work for me unless I count the number of occurences per combination beforehand?

Either way, thanks for your comment. I think Marco already found a solution for me though

[jensmunnichs]

Thank you Marco, this seems like the right solution to my problem. I figured something out yesterday after finding this old thread: https://community.qlik.com/thread/261640‌ (last comment all the way at the bottom) but as the original poster said, that method seems very inefficient. I attached the file where I made that method work for me anyway in case you or anyone else is interested.

I have another question if you don't mind. Would it be possible to make it so that there is only one listbox that shows every unique option for Product1 and Product2, and when someone selects a product from that list, it shows every combination where the chosen product is Product1 OR Product2 in the table?

Again thank you in advance, hope it is/wasn't too much trouble.

[MarcoWedel]

glad you liked the solution.

Please close this thread if your question is answered:

Qlik Community Tip: Marking Replies as Correct or Helpful

thanks

regards

Marco

[jensmunnichs]

In case anyone sees this and is curious about my second question: I found a solution, though it's probably not the most elegant one. I ended up using the original Product field as a listbox, and then using the following expression in the table:

If(isnull(GetCurrentSelections(Product)),Count({1} DISTINCT [Sales Order ID]),

Count({1<Product1=$::Product>+1<Product2=$::Product>} DISTINCT [Sales Order ID]))

So basically, I first check whether anything's selected for Product. If not, I show all combinations. If something is selected, I use set analysis to show all combinations where Product1 = Product or Product2 = Product. As I said, not the most elegant solution but it's all I've got and it works. Could have also loaded a list of products completely seperate from the rest of the data model but I figured I may aswell use the original list since it's there anyway.

If anyone knows of a better way to do this please let me know! I attached the file that contains my proposed solution.