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

[SerhanKaraer]

Hello @K7pramod 

Fractile function does the work. Just have a look at the help site.

https://help.qlik.com/en-US/sense/November2022/Subsystems/Hub/Content/Sense_Hub/Scripting/Statistica...

[K7pramod]

thanks @SerhanKaraer 
But i have the percentile values, my requirement is to list down the rows falling between these percentiles like

Select * from MyTab where RowNum BETWEEN Floor(PercentileMark90)
and CEILING(PercentileMark90)

For doing this i need to have the rownum assigned to each row of my data, is there any compatible Qlik sense script for below SQL

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

[barnabyd]

G'day @K7pramod,

I don't have a comment on the percentile logic as I don't know how 'PercetileMark90' is derived, but I can give you the Qlik equivalent of your 'partition by' statement ...

Table:
load *,
     ahs & '-' & num( md ) as group_key;
load id,
     APP_HOST_SYSTEM as ahs,
     MSG_DATETIME    as md,
     DURATION        as dur
from source.qvd (qvd);

Rollup:
left join (Table)
load group_key
   , rownum() as RowNum
resident Table
group by group_key
order by dur;

The difference between SQL and Qlik is you need to do the 'group by' into a separate table and then join it back into the main table. I hope this helps.

Cheers, Barnaby.

[K7pramod]

thanks @barnabyd , I tried the above code and getting some issues in group by as it needs the aggregation for using the group by - Below error I am getting - 

The following error occurred:

Aggregation expressions required by GROUP BY clause.

The error occurred here:

Rollup: left join (Table)

load group_key,

RowNo() as RowNum

resident Table

group by group_key

order by dur

[barnabyd]

Sorry, I often forget this piece. Everything that is not part of the group by, needs to be in some sort of aggregation expression, such as max(), avg() etc. In this case, I think we need to change RowNo() to only( RowNo() ).

[K7pramod]

thanks @barnabyd  looks like this works but uses more memory while joining the RowNum to the main table.

Table:
load *,
ahs & '-' & num( md ) as group_key;
load
APP_HOST_SYSTEM as ahs,
MSG_DATETIME as md,
DURATION as dur,
CountFlag
resident CPD_IBL;

Rollup:
left join (Table)
load group_key,
Only(RowNo()) as RowNum
resident Table
group by group_key
order by dur;

Left Join(CPD_IBL)
Load
ahs as APP_HOST_SYSTEM,
md as MSG_DATETIME,
RowNum
Resident Table;
drop Table Table;

It fails in the last left join as it takes more memory, like i have 16GB RAM machine running this query and it comes out with error -121.

[vinieme12 · Accepted solution]

As below

Load 

,Autonumber(APP_HOST_SYSTEM&DATE,APP_HOST_SYSTEM) as RowNum

APP_HOST_SYSTEM,
MSG_DATETIME,

DURATION

From SomeSource;

refer below

https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/Counter...

[K7pramod]

Looks like @barnabyd and @vinieme12  your solutions works well but it takes long time to process, Is there any way this can be simplified. the load gets aborted saying -121 error code.

[vinieme12]

"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."

Can you post some sample data