Smart way to calculate/find customers in load script

[qlikeers]

Hi!
I'm looking for a way to cleverly count customers who bought AT LEAST x products (e.g. 2, or 5 - freely programmable) from the list during ONE TRANSACTION.

So - what do I have:
1) A list of products to be taken into account. /ProdID/
2) A table with sales. /Date, TransID, CustID, ProdID, Qty, Sales/

I know that I can group it appropriately, throw out only the sales of these products from the list, add a column with the flag '1' and count at the end. However, I'm looking for a "smarter" way. Maybe someone could suggest something?

[Qrishna]

sample data and expected output view pls?

[qlikeers]

I don't have the data ready yet. But we're assuming columns like in the first post. You can use any sales data and a random list of products. Sample data set:

https://community.tableau.com/s/question/0D54T00000CWeX8SAL/sample-superstore-sales-excelxls

Let's say we take 10 random products for a closed list of products and assume, for example, 2 products that must be assigned to one order.

[Kushal_Chawda]

@qlikeers  If you want to count the customers who bought x products it is better to take front end expression approach to dynamically filter customers who bought x products.

1) Create variable input object (using variable let's say vProductN )with drop down or input box. If you create a drop down, create dynamic values using below expression =concat(ValueLoop(1,10),'|'). 

2) Create a measure to count customers who bought x product. You might need to change measure according to the definition of purchase

=Count ({<CustomerID ={"=count(distinct ProductID)>=$(vProductN)"}>} distinct CustomerID)

[qlikeers]

I just want to do it at the load editor level - in the code.

[Kushal_Chawda]

@qlikeers  I don't think you can do dynamically in the script. You will get fix numbers of purchases like 2,3 or 5 which will be fixed. But let's say user wants dynamic like they want to input it and depending on that they should get a values, then you can't do it in script. Also, it is not good practice to do it in script.

[Qrishna]

Try below:

Data:
LOAD Recno() as Sno,
[Customer ID] & '|' & [Order ID] as %CompKey,
[Order ID],
[Order Date],
[Customer ID],
[Product ID],
Quantity,
Sales
FROM
[..\2491319 - calculatefind customers in load script - curated_per_Req.xls]
(biff, embedded labels, table is Sheet1$);

left join(Data)
d:
load %CompKey,
count([Product ID]) as prod_count,
sum(Sales) as tot_amt_paid
resident Data
group by %CompKey;

qualify *;
//cust_atleast_x_prods:
c:
load *
resident Data
where prod_count>1;

[qlikeers]

No, no. I think we misunderstood each other. I can enter the number of products manually. I just want it to be possible to set it.

[qlikeers]

Exactly, I did it in a very similar way. You could call it classic 🙂

I wonder if it can be done even faster. Definitely. Anyway, thanks to everyone for helping!

[Kushal_Chawda]

@qlikeers  If you want more creative way, you could use window() function.

Data:

Load *, Window(Count(Product),CustID,Date) as cnt_prd 
Inline [
Date,CustID,Product,Sales
01/01/2024,1,A,100
01/01/2024,1,B,50
01/01/2024,1,C,70

01/02/2024,1,A,100
01/02/2024,1,B,50 ];

then on frond end you can use expression

=count({<cnt_prd={">2"}>} distinct CustID)