How to pull top 100 records from a fact table based on a dimension

[Anonymous]

Below is the script  and For each Division i need top 100 customers based on the Sales Amount.

LOAD  Division,

     [Customer number],     

     sum([Sales Amount]) as [Sales Amount]

FROM

V:\Datamart\QVHome\Data\BISalesStatistics.qvd

(qvd)

where FLAG_TTM=1

Group by

Division,

[Customer number],

FLAG_TTM;

Thanks for your help in advance.

[#2]

try this

LOAD  Division,

     [Customer number],     

     sum([Sales Amount]) as [Sales Amount]

FROM

V:\Datamart\QVHome\Data\BISalesStatistics.qvd

(qvd)

Group by

Division,

[Customer number],

FLAG_TTM

where FLAG_TTM=1 ;

and in expression write

if(Rank([Sales Amount]<=100),[Sales Amount] )

[Anonymous]

Thank you for your reply Vishwaranjan.. I will try this but is there any way to do the same thing in script.

[#4]

load table in decending order.then

Have you tried the FIRST prefix? From the Help:

The first prefix to a Load or Select (SQL) statement is used for loading just a set maximum number of records from the data source, regardless of its length.

The syntax is:

first n( loadstatement | selectstatement )

where:

n is an arbitrary expressing which evaluates to an integer indicating the maximum number of records to be read.

Examples:

First 10 Load * from abc.csv;

First (1) Select * from Orders; (Note: The parenthesis is allowed but not required.)

[RedSky001]

FIRST will work but will exlude the rows.  Use the example below if you want to return all rows and filter in the UI:

LOAD  *

,if(RowNo()<=100,1,0) as Top100

Resident YourTable

ORDER BY [Sales Amount] DESC

[#6]

try this

tab:

LOAD  Division,

     [Customer number],     

      [Sales Amount]

FROM

V:\Datamart\QVHome\Data\BISalesStatistics.qvd

(qvd);

tabnew:

load

Division,

     [Customer number],     

      [Sales Amount]

Resident tab

Order by

[Sales Amount]  desc;

drop table tab;

Result:

First 100 LOAD  Division,

     [Customer number],     

     sum([Sales Amount]) as [Sales Amount]

FROM

V:\Datamart\QVHome\Data\BISalesStatistics.qvd

(qvd)

where FLAG_TTM=1

Group by

Division,

[Customer number],

FLAG_TTM;

[Anonymous]

Hi Vishwaranjan thanks a lot on your help, But When i write the First 100 LOAD  Division, script is throwing me an error.

[Anonymous]

Hi Mohit thank you for your reply can you please help me on how to do that in qlikview script.