Displaying products without sales in a specific category and region

[SBurford]

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!

[sunny_talwar]

What if a SKU isn't sold in 2 or more Region? How would the output need to look like?

[SBurford]

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!

[sunny_talwar · Accepted solution]

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'

[SBurford]

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!