Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider - Maximize your investment, Join this webinar, May 26th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
SBurford
Contributor II
Contributor II

Displaying products without sales in a specific category and region

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:

SKUCategoryRegionSales (this year)
ADrinks110
ADrinks220
BSnacks130
BSnacks340
CDrinks250
CDrinks360
DSnacks170
DSnacks280

 

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:

SKURegion
B2
D3

 

Thanks for your help!

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

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'

image.png

View solution in original post

4 Replies
sunny_talwar

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

SBurford
Contributor II
Contributor II
Author

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:

SKUCategoryRegionSales (this year)
ADrinks110
ADrinks220
BSnacks130
CDrinks250
CDrinks360
DSnacks170
DSnacks280

 

Then, the output should look as follows:

SKURegion
B2
B3
D3

 

Thank you very much for your help!

 

sunny_talwar

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'

image.png

View solution in original post

SBurford
Contributor II
Contributor II
Author

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!