HOW TO GET TOP TEN SUM OF VALUE IN LOADING SCRIPT

[mahnoor1279]

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;

[BrunPierre]

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]

Its not giving the top ten results

[BrunPierre]

Post sample data.

[Hania]

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]

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]

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 · Accepted solution]

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

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;