How to get row count for a group order by date

[K7pramod]

Hello,

Can anyone help me with equivalent Qlik Sense code(LOAD statement) for the below SQL 

ROW_NUMBER() over (PARTITION by APP_HOST_SYSTEM,CAST(MSG_DATETIME as DATE) ORDER BY DURATION) as RowNum

I need a incremental number for the group APP_HOST_SYSTEM and MSG_DATETIME order by DURATION, this is to find the top delayed responses with 90th 95th percentile.
the table has these columns -
APP_HOST_SYSTEM,
MSG_DATETIME,

DURATION

[vinieme12 · Accepted solution]

two options

Option1) Autonumber()

,AutoNumber(APP_HOST_SYSTEM&date&DURATION,APP_HOST_SYSTEM) as rownum

Option2) Peek(), needs table to be sorted

Temp:

Load * from SomeTable;

Final:

Load *

,if(APP_HOST_SYSTEM=peek('APP_HOST_SYSTEM') and date=peek('date'),peek('rownum2')+1,1) as rownum2

Resident Temp

Order by APP_HOST_SYSTEM,date,DURATION;

[K7pramod]

@vinieme12  thanks a lot for your efforts and time. the solution worked and i am able to get the row numbers correct with below code.

Final:

Load *

,if(APP_HOST_SYSTEM=peek('APP_HOST_SYSTEM') and date=peek('date'),peek('rownum2')+1,1) as rownum2

Resident Temp

Order by APP_HOST_SYSTEM,date,DURATION;