Calculate nb of buyers of multiple products on the same day

[fab_castanheira]

Hello everyone,

I have the following requirement : count the number of clients who have bought at least 2 different products on the same day.

Here's an example with simplified data (let's assume Client is an unique identifier and we can do a distinct count over it) :

Date	Client	Product	Quantity
01/06/2022	Alice	P1	1
01/06/2022	Alice	P2	1
01/06/2022	Bob	P1	2
01/06/2022	Bob	P1	1
01/06/2022	Charlie	P1	3
02/06/2022	Alice	P2	1
02/06/2022	Charlie	P2	2

The expected result here would be 1 client (Alice) who bought both products P1 and P2 on the same day.

Bob bought 2 products on the same day but they were both the same product, whereas Charlie bought 2 different products but on different days.

Would somebody be able to help me find the right expression for this ?

Thanks!

[sidhiq91]

I have tried something in Script Load itself. Please see below and let me know if it  has helped.

NoConcatenate
Temp:
Load * Inline [
Date,Client,Product,Quantity
01/06/2022, Alice, P1, 1
01/06/2022, Bob, P1, 2
01/06/2022, Bob, P1, 1
01/06/2022, Charlie,P1, 3
02/06/2022, Alice,P2,1
02/06/2022, Charlie,P2, 2
01/06/2022, Alice, P2, 1
];

NoConcatenate
Temp1:
Load *,
if(Date=Peek(Date) and Peek(Product)<>Product and peek(Client)=Client
,'Eligible','Not Eligible') as [Criteria]

Resident Temp

Order by Date,Client;

Drop Table Temp;

Exit Script;