topic Re: Identify set of items in QlikView

Identify set of items

Thu, 19 May 2016 12:57:24 GMT

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

Re: Identify set of items

Anonymous — Sat, 21 May 2016 01:35:55 GMT

Hi Tobias,

Could you please share a qvw with some sample data.

Regards,

-- Karla

Re: Identify set of items

Gysbert_Wassenaar — Sat, 21 May 2016 09:27:01 GMT

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)

Re: Identify set of items

Wed, 01 Jun 2016 09:27:29 GMT

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.

Re: Identify set of items

sunny_talwar — Fri, 03 Jun 2016 02:08:31 GMT

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?

Re: Identify set of items

Fri, 03 Jun 2016 06:58:20 GMT

Hello Sunny T,

yes that would be my preferred solution.