Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik experts,
I'm interested in showing the products that haven't had sales in a particular category.
Before continuing, I have already checked multiple sources in Qlik Sense community, but with no success, because the answer has proven only to be applicable to the simpler version of my problem as in https://community.qlik.com/t5/New-to-Qlik-Sense/Show-customers-with-0-sales/td-p/1560212
I'll display what would be pretty much the structure of my dataset:
SKU | Category | Region | Sales (this year) |
A | Drinks | 1 | 10 |
A | Drinks | 2 | 20 |
B | Snacks | 1 | 30 |
B | Snacks | 3 | 40 |
C | Drinks | 2 | 50 |
C | Drinks | 3 | 60 |
D | Snacks | 1 | 70 |
D | Snacks | 2 | 80 |
For instance, lets say that I'm only interested in the products that weren't sold in Regions 1, 2 and 3 of the "Snacks" Category. In this particular case, the B and D weren't sold in Regions 2 and 3, respectively.
The expected result would be the following table:
SKU | Region |
B | 2 |
D | 3 |
Thanks for your help!
Script manipulation
Table:
LOAD *,
SKU&Region as Key;
LOAD * INLINE [
SKU, Category, Region, Sales (this year)
A, Drinks, 1, 10
A, Drinks, 2, 20
B, Snacks, 1, 30
C, Drinks, 2, 50
C, Drinks, 3, 60
D, Snacks, 1, 70
D, Snacks, 2, 80
];
tmpMissRegionTable:
LOAD Distinct SKU,
Category
Resident Table;
Join (tmpMissRegionTable)
LOAD Distinct Region
Resident Table;
Concatenate (Table)
LOAD SKU,
Category,
Region
Resident tmpMissRegionTable
Where not Exists (Key, SKU&Region);
DROP Table tmpMissRegionTable;
Front end chart
Dimensions
SKU
Region
Expression
=Sum([Sales (this year)]) = 0 and Category = 'Snacks'
What if a SKU isn't sold in 2 or more Region? How would the output need to look like?
Hello Sunny_talwar, thank you for your excellent remark. I didn't consider it in my original question.
Imagine that product B (Category "Snacks") was only sold in Region 1. Then my example would be:
SKU | Category | Region | Sales (this year) |
A | Drinks | 1 | 10 |
A | Drinks | 2 | 20 |
B | Snacks | 1 | 30 |
C | Drinks | 2 | 50 |
C | Drinks | 3 | 60 |
D | Snacks | 1 | 70 |
D | Snacks | 2 | 80 |
Then, the output should look as follows:
SKU | Region |
B | 2 |
B | 3 |
D | 3 |
Thank you very much for your help!
Script manipulation
Table:
LOAD *,
SKU&Region as Key;
LOAD * INLINE [
SKU, Category, Region, Sales (this year)
A, Drinks, 1, 10
A, Drinks, 2, 20
B, Snacks, 1, 30
C, Drinks, 2, 50
C, Drinks, 3, 60
D, Snacks, 1, 70
D, Snacks, 2, 80
];
tmpMissRegionTable:
LOAD Distinct SKU,
Category
Resident Table;
Join (tmpMissRegionTable)
LOAD Distinct Region
Resident Table;
Concatenate (Table)
LOAD SKU,
Category,
Region
Resident tmpMissRegionTable
Where not Exists (Key, SKU&Region);
DROP Table tmpMissRegionTable;
Front end chart
Dimensions
SKU
Region
Expression
=Sum([Sales (this year)]) = 0 and Category = 'Snacks'
Great answer! I think I can work myself out from this point, since some simpler questions that might arise from this question are already answered somewhere else in the forum.
Thank you very much!