Discussion Board for collaboration on QlikView Scripting.
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.
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
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.
Go to Solution.
Something like this:
if(SystemID=previous(SystemID),peek('Rank')+1,1) as Rank
ORDER BY SystemID, Timestamp;
look into using functions peek() and previous() to build a running total that you can reset with each new SystemID.
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.