topic Re: Re: Straight table Top 100 in QlikView

Straight table Top 100

Mon, 28 Jul 2014 05:49:07 GMT

I have

Zone Customer Revenue

There are 10 zones; I want top 100 revenue generating customers from every zone that is I want 1000 customers in one straight table.

Is it possible??

Re: Straight table Top 100

amit_saini — Mon, 28 Jul 2014 05:53:08 GMT

Aamir,

Can be achieved by Rank function, please see below a simple example:

Syntax:

=rank(sum(Investment),1,1)

AND

=aggr(if(Rank(sum(Investment))<=3,Investment),Investment) (For calculating Top 3)

AND

=if(rank(sum(Investment),1,1)=1,'# 1')

I have a data table which includes the following fields; SystemID, Timestamp.

I want to derive a new field in this table which ranks the Timestamp in ascending grouped by SystemID

I'd like to do this in the script.

e.g.

SystemID, Timestamp, [Rank]

00001, 01/01/2013 10:00, 1

00001, 01/01/2013 12:00, 2

00001, 01/01/2013 12:30, 3

00002, 01/01/2013 11:00, 1

00002, 01/01/2013 12:12, 2

00003, 01/02/2013 10:30, 1

Syntax:

LOAD

SystemID, Timestamp,

if(SystemID=previous(SystemID),peek('Rank')+1,1) as Rank

FROM ...mysource...

ORDER BY SystemID, Timestamp;

Thanks,

Re: Straight table Top 100

Mon, 28 Jul 2014 06:04:46 GMT

Can you please do it for my case.

Do I need to do changes in my script. Can't put rank in my straight table

Re: Re: Straight table Top 100

amit_saini — Mon, 28 Jul 2014 06:08:33 GMT

Aamir,

You can do this either at script side or you can call this function as calculated expression also. For example purpose please see the attachment,

Thanks,
AS

Re: Re: Straight table Top 100

Mon, 28 Jul 2014 06:16:34 GMT

I have two dimensions

Re: Re: Straight table Top 100

Mon, 28 Jul 2014 06:18:36 GMT

Sample Data:

For this I want top 1 for each zone

Zone	Customer	Revenue
A	1	1000
A	2	1500
A	3	2000
B	4	1100
B	5	1400
B	6	2200

Re: Straight table Top 100

CELAMBARASAN — Mon, 28 Jul 2014 06:24:28 GMT

Check with the Dimension limits tab and as apply the settings as below

Re: Straight table Top 100

Mon, 28 Jul 2014 06:28:45 GMT

Please reread the issue

Re: Re: Straight table Top 100

CELAMBARASAN — Mon, 28 Jul 2014 06:39:07 GMT

As per the issue stated, dimension limits work for you.

Please check the attached

Re: Re: Straight table Top 100

Mon, 28 Jul 2014 06:50:27 GMT

Oh yes It is working. The problem was I had put zone after customers

Re: Re: Straight table Top 100

CELAMBARASAN — Mon, 28 Jul 2014 06:53:05 GMT

Glad that it worked.