creating new id and order by timestamp

[#1]

Hi Everybody,

I have the following issue to solve - I have a table wirh UserId, VisitId and timestamp and now need to put visits for each user in chronological order.

how can I create a new ID which ranks the users visits chronologically.

In SQL speak I would first group by UserId and the rank the visits within each group by timestamp. Is ther anything comparable in qlikview?

I have:

userid | visitid | timestamp

123     |   abc | 222111

123     |   der  | 111133

123     |   der  | 543435

123     |    fgg | 353453

123     |   gdfg | 435354

456     |   ettt  | 254566

456     |   gdfg | 164666

789     |   hfd   | 266655

789     |   dhf   | 365656

789     |   dhdh| 156566

i need:

userid | visitid | timestamp| NewId

123     |   abc | 222111 | 2

123     |   der  | 111133 | 1

123     |   der  | 543435 | 5

123     |    fgg | 353453 | 3

123     |   gdfg | 435354 | 4

456     |   ettt  | 254566 | 2

456     |   gdfg | 164666 | 1

789     |   hfd   | 266655 | 2

789     |   dhf   | 365656 | 3

789     |   dhdh| 156566 | 1

Thx in advance,

Pqi

[swuehl]

Try something like

LOAD userid, visitid, timestamp,

if(peek(userid)=userid, peek(NewId)+1,1) as NewId

from Table order by userid, timestamp;