Compare values within a customer group / pivot table columns

[beijerltd]

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

[edwin]

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:

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

and this is your result:

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

[vidyutverma]

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.