Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
beijerltd
Contributor
Contributor

Compare values within a customer group / pivot table columns

Hello,

I tried searching for a similar issue but could not find a solution.

I need to compare customer discounts for a subset of accounts and if they are not the same or some are missing flag it as an issue.

Ideally the output would be displayed in a pivot table with product code as the dimension and customer account number as the columns.

Example data attached.

For example:

A master customer has 5(number varies) different accounts across the group. Each account will have various discounts, some could have none at all. Once the master customer is selected, the output would compare the discounts across these 5 accounts for each product and if:

1. They are not the same

2. Some are missing

Create a flag to filter them on and all the issues be visible in a pivot table.

Kind regards

Labels (1)
2 Replies
edwin
Master
Master

i would backfill missing entries with or a negative number that will surely not be in your discounts.  the reason i would do that is a missing data point will be null and it will be hard to capture.

then i will just count the distinct number of discounts - if =1 then all discounts are the same and none are missing, else not all discounts are the same or at least one is missing:

tmp1:
load Distinct
Master, Cus
Resident data;

tmp2:
load Distinct
Master, Prod
Resident data;

inner join (tmp1)
load *, 0 as [Discount%]
Resident tmp2;

Concatenate (data)
load * Resident tmp1;

drop table tmp1, tmp2;

NoConcatenate
newData:
load Master,Cus,Prod,max([Discount%]) as [Discount%]
Resident data
group by Master,Cus,Prod;

drop table data;

this is the expression wil be:

if(aggr(nodistinct Count(distinct [Discount%]),Prod)<>1,'ALERT', 'OK')

this is the data for 1st master, except for prod1, i changed prod 1 and 2 so  there will at least be one row that will pass, and 1 row that will not have a missing discount but will have different discounts:

edwin_1-1659736356333.png

this is how it looks like after backfilling it with 0 discounts:

edwin_2-1659736417535.png

and this is your result:

edwin_3-1659736450483.png

only 1 row has uniform discounts and the rest have either non-uniform or missing discounts

 

vidyutverma
Partner - Contributor III
Partner - Contributor III

Assuming that You have a table like this:

Load * Inline [
Product, Customer, Discount
Prod1,Cust1,10
Prod1,Cust2,20
Prod1,Cust3,10
Prod2,Cust1,10
Prod2,Cust3,10
Prod3,Cust1,20
Prod3,Cust2,0
Prod4,Cust1,30
Prod5,Cust1,10
Prod5,Cust2,10
Prod5,Cust3,10
];

Then As you want to flag the products having the issue, You do not need a Pivot table. Having a straight table for Products with Product as Dimension and following Expression should give you the desired result:

If(Count(Discount)=Count(Distinct Total Customer) and Count(Distinct Discount)=1,'OK','Issue')

 

I generally prefer doing this flag generating in the Script, to keep the expressions simple in the frontend. Using count Distinct is bad for performance if your dataset is large. So you could use the method suggested by @edwin  and add Alert generation as the last step for each product.