Identify set of items

[#1]

Hello,

I'm struggling to find a solution for the folling issue. I have two tables. One is including sales data on voucher level including item numbers and the quantity of sold items. The other table is showing a list of "item sets" e.g. combinations of sold items that I would like to identify.

The result should bei a table that shows only sold voucher numbers that include the specified items that have been sold in a set (together) with dedicated "add on items". In other words: Show vouchers that include item A and item B or item A  and item C.

As I do have a lot of items and possible combinations and cannot "hardcode" all the combinations in a set analysis.

Instead I would like to load tables that can be exchanged.

For better understanding I have included a MS Excel file with the respective tables, I have highlighted the data that meet the critieria in green color.

Thanks for your help,

Tobias

[Anonymous]

Hi Tobias,

Could you please share a qvw with some sample data.

Regards,

-- Karla

[Gysbert_Wassenaar]

You can try this for example:

[Sales Data]:
LOAD
  [Voucher number],
      [Item number],
      Quantity
FROM
  [Item Set Example.xlsx]
  (ooxml, embedded labels, table is [Sales Data])
  ;

Temp1:
CROSSTABLE(Set,Item2)
LOAD
  [Item number] as Item1,
      [Set item number 1],
      [Set item number 2]
FROM
  [Item Set Example.xlsx]
  (ooxml, embedded labels, table is [Set item])
  ;

Sets:
CROSSTABLE(Item,[Item number])
LOAD
  Set,
  Item1,
  Item2
RESIDENT
  Temp1
  ;

DROP TABLE Temp1;

Then add a straight table to your document with Voucher and Item number as dimensions and an expression like this one: count({<[Voucher number]={"=count(distinct Item)=2"}>}DISTINCT Item)

[#4]

Hello,

I like to share some sample data.

I made it up to the point, where I need to match the bundle article with the voucher article.

If have thought of some match or wildmatch function that is searching for the 'Bundle' in the 'Multiple Items' field.

The sorting of the article that make up the bundle is not relevant.

[sunny_talwar]

So both the items in the Bundle needs to match up with Multiple Items to be included in the calculation? And this is needed in the script?

[#6]

Hello Sunny T,

yes that would be my preferred solution.