How to write row_number with partition by in qlik table?

Question

I want to write below row_number logic in qlik table. How can it be written?

row_number() over (partition by job.jobnumber order by cast(hist.EventTimeStamp as Date) asc) as row_id

How to write partition by in row number in qlik sense?

Answer 1

You can achieve this by using the AutoNumber() function, which returns an incrementing integer for each unique value provided grouped by an "AutoID." In your case, you can use it like so:

[Jobs]:
Load
    AutoNumber(RowNo(), [job.jobnumber]) as [row_id]
  , [job.jobnumber]
  , [hist.EventTimeStamp]
  , [EventType]
  , [EventValue]
Resident [data]
  Order By [hist.EventTimeStamp] asc
;

EDIT:

Come to think of it, you can also use the Window() function to achieve partition functionality in Qlik. It's a relatively new function so you may or may not have it at your disposal depending on the version of Qlik you're using.

You would use it like this:

[Jobs]:
Load
    Window(RecNo(), [job.jobnumber], 'ASC', [hist.EventTimeStamp])
  , [job.jobnumber]
  , [hist.EventTimeStamp]
  , [EventType]
  , [EventValue]
Resident [data];