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;
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
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.
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 -
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() ).
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.
As below
Load
,Autonumber(APP_HOST_SYSTEM&DATE,APP_HOST_SYSTEM) as RowNum
APP_HOST_SYSTEM,
MSG_DATETIME,
DURATION
From SomeSource;
refer below
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.
"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