topic Re: How to pull top 100 records from a fact table based on a dimension in QlikView

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

Anonymous — Tue, 21 May 2013 13:12:50 GMT

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.

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

Tue, 21 May 2013 13:19:23 GMT

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] )

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

Anonymous — Tue, 21 May 2013 13:21:25 GMT

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

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

Tue, 21 May 2013 13:33:02 GMT

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.)

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

RedSky001 — Tue, 21 May 2013 13:37:52 GMT

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

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

Tue, 21 May 2013 13:47:50 GMT

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;

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

Anonymous — Tue, 21 May 2013 14:23:17 GMT

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

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

Anonymous — Tue, 21 May 2013 14:24:08 GMT

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