Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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;
How is a set defined?
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.
Do they need to be sold in the same month, or does the month not matter?
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;
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.
The solution idea is very interesting and cool! Thanks 🙂