Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Rank one field grouping by another field

Hello,

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

etc etc

Is there a function that does this or is the only option to create some code that loops through the whole recordset?

Thanks for any help.

Adam

                                    

Tags (3)
1 Solution

Accepted Solutions

Re: Rank one field grouping by another field

Something like this:

LOAD

SystemID, Timestamp,

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

FROM ...mysource...

ORDER BY SystemID, Timestamp;


talk is cheap, supply exceeds demand
3 Replies
MVP
MVP

Re: Rank one field grouping by another field

look into using functions peek() and previous() to build a running total that you can reset with each new SystemID.

Re: Rank one field grouping by another field

Something like this:

LOAD

SystemID, Timestamp,

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

FROM ...mysource...

ORDER BY SystemID, Timestamp;


talk is cheap, supply exceeds demand
Not applicable

Re: Rank one field grouping by another field

Thanks,

I had to use the ORDER BY in my first table from which then I used a resident load from with the above Rank function. It wasn't working when I did the ORDER BY in the same load.

Great solution though.

Thanks again

Adam

Community Browser