Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
@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;