topic Reduce data in script to top 50 customers in QlikView

Reduce data in script to top 50 customers

sifatnabil — Fri, 20 Oct 2017 12:57:17 GMT

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?

Re: Reduce data in script to top 50 customers

alexandros17 — Fri, 20 Oct 2017 13:14:03 GMT

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)

Re: Reduce data in script to top 50 customers

effinty2112 — Fri, 20 Oct 2017 13:20:31 GMT

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