topic Re: Rank one field grouping by another field in QlikView

Rank one field grouping by another field

Fri, 01 Feb 2013 15:10:47 GMT

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

Re: Rank one field grouping by another field

Oleg_Troyansky — Fri, 01 Feb 2013 18:13:30 GMT

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

Gysbert_Wassenaar — Fri, 01 Feb 2013 18:44:50 GMT

Something like this:

LOAD

SystemID, Timestamp,

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

FROM ...mysource...

ORDER BY SystemID, Timestamp;

Re: Rank one field grouping by another field

Mon, 04 Feb 2013 09:27:08 GMT

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