Reduce data in script to top 50 customers

[sifatnabil]

Hi all,

I have a large dataset but customer revenues for the top 50 customers are relevant, while the others are all insignificant and just noise. Is there a way to only load the top 50 customers by revenue in script and drop the rest?

[alexandros17]

Suppose you have this table:

Invoice with Customer and Amount Field

Every Database has an instruction to exctract the top n values (There are "Limit" or "TOP" Keawords for instance)

a query like this one:

select customer, sum(Amount) as somma from invoice group by customer order by somma desc limit 50

extract the first 50 then do an inner join of this one with the complete table of invoice (invoice is obviously for example)

[effinty2112 · Accepted solution]

Hi Sifat,

Maybe something like:

Top50:

LOAD Customer,

    OrderValue

From Orders;

Aggregate:

LOAD

Customer,

Sum(OrderValue) as TotalSales

Resident Top50 Group by Customer;

Inner Join(Top50)

Load

Customer

Resident Aggregate Where RecNo() <=50 Order by TotalSales desc ;

Drop Table Aggregate;

Regards

Andrew