Counting sets in LOAD SCRIPT

[qlikeers]

Hi!

I have this case -> there is one table that contains data:
- Seller number,
- Month
- Client number
- Product code
- Qty
- The value of sales

We have two sets of products:
1) Prodct code: 'ABC' and 'DEF'
2) Product code: 'ABC' and GHI'

What's the easiest way (in load script) to count how many sets each seller sold?

[LRuCelver]

How is a set defined?

• Is it the number of sales of either 'ABC' or 'DEF'?
• Is it the number if 'ABC' and 'DEF' sold together to a single client?
• Is it something else?

[qlikeers]

Two products must be sold to one customer. Minimum of each piece. E.g. selling 1x'ABC' and 1x'DEF' to one customer. Sets for one customer can be duplicated, i.e. the customer can buy 3 sets.

[LRuCelver]

Do they need to be sold in the same month, or does the month not matter?

[LRuCelver · Accepted solution]

I have included the month. You should be able to easily remove it:

//	Loading Data required
Data:
NoConcatenate Load 
	Seller,
    Date(Date#(Month, 'MMM YYYY'), 'MMM YYYY') as Month,
    Client,
    Product,
    Qty
Inline [
	Seller,	Month,		Client,	Product,	Qty
	1,		Jan 2024,	1,		ABC,		2
	1,		Jan 2024,	2,		ABC,		1
	1,		Jan 2024,	2,		DEF,		3
	1,		Feb 2024,	1,		DEF,		2
	2,		Jan 2024,	1,		DEF,		2
	2,		Feb 2024,	1,		ABC,		5
	2,		Feb 2024,	1,		DEF,		5
	2,		Mar 2024,	3,		ABC,		1
	2,		Mar 2024,	3,		GHI,		10
];

Sets:
NoConcatenate Load * Inline [
	Set,	Product
    1,		ABC
    1,		DEF
    2,		ABC
    2,		GHI
];


//	Create table with all possible combinations of seller, month and client with product and set
Combinations:
NoConcatenate Load Distinct
	Seller, 
    Month, 
    Client
Resident Data;

Join Load * Resident Sets;

Join Load * Resident Data;

Drop Tables Data, Sets;


//	Get the smallest quantity per combination and discard all where no set was sold
Aggregated:
NoConcatenate Load
	*
Where SetQty > 0;
Load
	Client,
    Month,
    Seller,
    Set,
    Min(RangeMax(Qty, 0)) as SetQty
Resident Combinations
Group By
	Client,
    Month,
    Seller,
    Set;

Drop Table Combinations;

[qlikeers]

Thanks!

The month matters. It should count up to a month.
Actually the client doesn't matter, it should count the sets, but actually the client can be visible. We can finally sum it up.

[qlikeers]

The solution idea is very interesting and cool! Thanks 🙂