Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
mahnoor1279
Contributor III
Contributor III

HOW TO GET TOP TEN SUM OF VALUE IN LOADING SCRIPT

Hello everyone,

I want to get the top ten Customers from specific fund, although I've  group by the customers with the FEE but i didn't get how to get only TOP 10 customer from SUM(FEE).

Note: I want script editor technique 

Here is the sample script 

AUM1:
 LOAD
 
 
     CUST_ACCT,
     FEE,
     Num#(FUND_SNO) as FUND_SNO
     
   
FROM [lib://DataExtractor/MFEE.qvd](qvd)
WHERE NUM#(FUND_SNO) ='20224252413';
 
 
NoConcatenate
tab2:
LOAD  CUST_ACCT,FUND_SNO,SUM(FEE) AS FEE
 
Resident AUM1 
group by CUST_ACCT,,FUND_SNO;
drop table AUM1;

 

Labels (4)
1 Solution

Accepted Solutions
BrunPierre
Partner - Master
Partner - Master

The script works fine. For example, the below shows the top 5 for the given data set.

BrunPierre_0-1688380102741.png

AUM1:
LOAD * Inline[
CUST_ACCT_SNO,FEE
502724629,7380481.684569 	
1106753357,19972219885.22 	
2847691279,17423120900 	
26455,8350738.524138 	
85868387,5733653.631537 	
2685127842,8409095847.7502 	
318824877,3260140385.0952 	
1297526159,5805658.244773 	
8957672615,8753530.206352 	
7517851445,41752339.468959];

Left Join(AUM1)
LOAD CUST_ACCT_SNO,
     Num(Sum(FEE),'#,##0.00') as TotalFEE
Resident AUM1
Group By CUST_ACCT_SNO; 

NoConcatenate
LOAD CUST_ACCT_SNO,
     TotalFEE
Resident AUM1
Where RecNo() <= 5
Order By TotalFEE desc;

DROP Table AUM1;
RENAME Field TotalFEE to FEE;

EXIT SCRIPT;

View solution in original post

7 Replies
BrunPierre
Partner - Master
Partner - Master

Try this:

AUM1:
LOAD CUST_ACCT,
FEE,
Num#(FUND_SNO) as FUND_SNO

FROM [lib://DataExtractor/MFEE.qvd](qvd)
WHERE NUM#(FUND_SNO) ='20224252413';

Left Join
LOAD CUST_ACCT,
Sum(FEE) as TotalFEE

Resident AUM1
Group By CUST_ACCT;

Noconcatenate
LOAD CUST_ACCT,
FUND_SN,
TotalFEE as FEE

Resident AUM1
Where RecNo() <= 10
Order By TotalFEE desc;

DROP Table AUM1;

mahnoor1279
Contributor III
Contributor III
Author

Its not giving the top ten results

BrunPierre
Partner - Master
Partner - Master

Post sample data.

Hania
Creator
Creator

hello,

You can display top 10 in frontend by setting condition in dimension.

Go to Dimension.

In Limitations select Fixed Number.

Click On Top.

Enter the 10.

Thanks 

Hope answer is useful  please mark as solution.

mahnoor1279
Contributor III
Contributor III
Author

Below sheet represent the desired results, however it'sworking fine with the aggr rank function but i want to load only the top ten results 

CUST_ACCT_SNO FEE if(aggr(rank(sum(FEE))<=10,CUST_ACCT_SNO),FEE)
502724629 7380481.684569 7380481.684569
1106753357 19972219885.22 19972219885.22
2847691279 17423120900 17423120900
26455 8350738.524138 8350738.524138
85868387 5733653.631537 5733653.631537
2685127842 8409095847.7502 8409095847.7502
318824877 3260140385.0952 3260140385.0952
1297526159 5805658.244773 5805658.244773
8957672615 8753530.206352 8753530.206352
7517851445 41752339.468959 41752339.468959

 

And i got only this result when loading the provided script

 

FUND_SNO CUST_ACCT_SNO FEE
2022060003 1106753357 19972219885.22
Mark_Little
Luminary
Luminary

Hi.

Keep you load Script as before, but ensure it is has

Order By TotalFEE desc;

Then for that  add

FIRST 10 LOAD

*

RESIDENT table;

DROP TABLE table;

BrunPierre
Partner - Master
Partner - Master

The script works fine. For example, the below shows the top 5 for the given data set.

BrunPierre_0-1688380102741.png

AUM1:
LOAD * Inline[
CUST_ACCT_SNO,FEE
502724629,7380481.684569 	
1106753357,19972219885.22 	
2847691279,17423120900 	
26455,8350738.524138 	
85868387,5733653.631537 	
2685127842,8409095847.7502 	
318824877,3260140385.0952 	
1297526159,5805658.244773 	
8957672615,8753530.206352 	
7517851445,41752339.468959];

Left Join(AUM1)
LOAD CUST_ACCT_SNO,
     Num(Sum(FEE),'#,##0.00') as TotalFEE
Resident AUM1
Group By CUST_ACCT_SNO; 

NoConcatenate
LOAD CUST_ACCT_SNO,
     TotalFEE
Resident AUM1
Where RecNo() <= 5
Order By TotalFEE desc;

DROP Table AUM1;
RENAME Field TotalFEE to FEE;

EXIT SCRIPT;