topic How to get row count for a group order by date in App Development

How to get row count for a group order by date

K7pramod — Mon, 13 Feb 2023 12:33:21 GMT

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

Re: How to get row count for a group order by date

SerhanKaraer — Mon, 13 Feb 2023 15:11:12 GMT

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/StatisticalAggregationFunctions/fractile.htm

Re: How to get row count for a group order by date

K7pramod — Mon, 13 Feb 2023 16:13:28 GMT

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

Re: How to get row count for a group order by date

barnabyd — Mon, 13 Feb 2023 21:02:55 GMT

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.

Re: How to get row count for a group order by date

K7pramod — Tue, 14 Feb 2023 04:27:31 GMT

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

Re: How to get row count for a group order by date

barnabyd — Tue, 14 Feb 2023 05:04:46 GMT

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() ).

Re: How to get row count for a group order by date

K7pramod — Tue, 14 Feb 2023 07:39:49 GMT

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.

Re: How to get row count for a group order by date

vinieme12 — Tue, 14 Feb 2023 08:56:30 GMT

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/CounterFunctions/autonumber.htm

Re: How to get row count for a group order by date

K7pramod — Tue, 14 Feb 2023 09:54:25 GMT

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.

Re: How to get row count for a group order by date

vinieme12 — Tue, 14 Feb 2023 10:09:59 GMT

"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

Re: How to get row count for a group order by date

K7pramod — Tue, 14 Feb 2023 11:55:19 GMT

@barnabyd , @vinieme12 , @SerhanKaraer

some of the formula used to calculate the value.

95th percentile = max(RowNum)-1*.950+1

90th Percentile = max(RowNum)-1*.900+1

Mean and Maximum are some random values.

the requirement is to get the rows falling between these percentiles, for that i wanted the rownum group by AHS & Date order by Duration.

the below logic will pick only rows falls between these percentiles.

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

My major requirement is to get the first column in below table in Qliksense load. i am not able to get the row number assigned to each rows group by AHS & date order by Duration.

Here is the sample data -

RowNum	PercentileMark95	PercentileMark90	Mean	Maximum	DURATION	APP_HOST_SYSTEM	date
1	9.55	9.1	2545	10924	1542	CAR	13/10/2022
2	9.55	9.1	2545	10924	1591	CAR	13/10/2022
3	9.55	9.1	2545	10924	1599	CAR	13/10/2022
4	9.55	9.1	2545	10924	1607	CAR	13/10/2022
5	9.55	9.1	2545	10924	1625	CAR	13/10/2022
6	9.55	9.1	2545	10924	1628	CAR	13/10/2022
7	9.55	9.1	2545	10924	1651	CAR	13/10/2022
8	9.55	9.1	2545	10924	1652	CAR	13/10/2022
9	9.55	9.1	2545	10924	1660	CAR	13/10/2022
10	9.55	9.1	2545	10924	1673	CAR	13/10/2022
1	10.5	10	3152	9717	1336	CAR	17/10/2022
2	10.5	10	3152	9717	1474	CAR	17/10/2022
3	10.5	10	3152	9717	1516	CAR	17/10/2022
4	10.5	10	3152	9717	1518	CAR	17/10/2022
5	10.5	10	3152	9717	1520	CAR	17/10/2022
6	10.5	10	3152	9717	1553	CAR	17/10/2022
7	10.5	10	3152	9717	1584	CAR	17/10/2022
8	10.5	10	3152	9717	1587	CAR	17/10/2022
9	10.5	10	3152	9717	1600	CAR	17/10/2022
10	10.5	10	3152	9717	1639	CAR	17/10/2022
11	10.5	10	3152	9717	1640	CAR	17/10/2022
1	9.55	9.1	17154	58769	1801	CAR	18/10/2022
2	9.55	9.1	17154	58769	2005	CAR	18/10/2022
3	9.55	9.1	17154	58769	2030	CAR	18/10/2022
4	9.55	9.1	17154	58769	2126	CAR	18/10/2022
5	9.55	9.1	17154	58769	2237	CAR	18/10/2022
6	9.55	9.1	17154	58769	2330	CAR	18/10/2022
7	9.55	9.1	17154	58769	2387	CAR	18/10/2022
8	9.55	9.1	17154	58769	2514	CAR	18/10/2022
9	9.55	9.1	17154	58769	2560	CAR	18/10/2022
10	9.55	9.1	17154	58769	2564	CAR	18/10/2022
1	11.45	10.9	5490	38626	667	DÉCOR	13/10/2022
2	11.45	10.9	5490	38626	687	DÉCOR	13/10/2022
3	11.45	10.9	5490	38626	692	DÉCOR	13/10/2022
4	11.45	10.9	5490	38626	698	DÉCOR	13/10/2022
5	11.45	10.9	5490	38626	702	DÉCOR	13/10/2022
6	11.45	10.9	5490	38626	704	DÉCOR	13/10/2022
7	11.45	10.9	5490	38626	706	DÉCOR	13/10/2022
8	11.45	10.9	5490	38626	707	DÉCOR	13/10/2022
9	11.45	10.9	5490	38626	709	DÉCOR	13/10/2022
10	11.45	10.9	5490	38626	711	DÉCOR	13/10/2022
11	11.45	10.9	5490	38626	712	DÉCOR	13/10/2022
12	11.45	10.9	5490	38626	715	DÉCOR	13/10/2022
1	14.3	13.6	7518	25689	692	DÉCOR	17/10/2022
2	14.3	13.6	7518	25689	700	DÉCOR	17/10/2022
3	14.3	13.6	7518	25689	705	DÉCOR	17/10/2022
4	14.3	13.6	7518	25689	707	DÉCOR	17/10/2022
5	14.3	13.6	7518	25689	721	DÉCOR	17/10/2022
6	14.3	13.6	7518	25689	724	DÉCOR	17/10/2022
7	14.3	13.6	7518	25689	727	DÉCOR	17/10/2022
8	14.3	13.6	7518	25689	736	DÉCOR	17/10/2022
9	14.3	13.6	7518	25689	739	DÉCOR	17/10/2022
10	14.3	13.6	7518	25689	760	DÉCOR	17/10/2022
11	14.3	13.6	7518	25689	769	DÉCOR	17/10/2022
12	14.3	13.6	7518	25689	774	DÉCOR	17/10/2022
13	14.3	13.6	7518	25689	781	DÉCOR	17/10/2022
14	14.3	13.6	7518	25689	792	DÉCOR	17/10/2022
15	14.3	13.6	7518	25689	798	DÉCOR	17/10/2022

Re: How to get row count for a group order by date

K7pramod — Thu, 16 Feb 2023 02:28:25 GMT

@vinieme12 @barnabyd @SerhanKaraer Any answers for this problem?

Re: How to get row count for a group order by date

vinieme12 — Thu, 16 Feb 2023 03:04:50 GMT

replace the below expression

=max(RowNum)-1*.950+1

With

=(count(distinct total <APP_HOST_SYSTEM,date> DURATION )-1)*.95+1

same for 90th

(count(distinct total <APP_HOST_SYSTEM,date> DURATION )-1)*.90+1

Re: How to get row count for a group order by date

K7pramod — Thu, 16 Feb 2023 03:20:26 GMT

@vinieme12 here my worry is not the percentile calculation, I need the row number column to be added to each rows group by APP_HOST_SYSTEM & date order by DURATION .

Re: How to get row count for a group order by date

vinieme12 — Thu, 16 Feb 2023 03:23:46 GMT

IF the purpose of RowNo is only for calculating percentile then you can replace it with the expression i provided above

If you still need RowNo in your front end table, then you can replace it with Rank()

aggr(rank(-DURATION),APP_HOST_SYSTEM,date,DURATION)

Re: How to get row count for a group order by date

K7pramod — Thu, 16 Feb 2023 03:31:38 GMT

@vinieme12 I do not have the first column in the data i load. based on the group by APP_HOST_SYSTEM & date order by DURATION, that column to be added to the table while LOAD. aggr can not be directly used in LOAD right?

Re: How to get row count for a group order by date

vinieme12 — Thu, 16 Feb 2023 03:37:07 GMT

these expressions are an alternative to creating the RowNo field at all

Re: How to get row count for a group order by date

K7pramod — Thu, 16 Feb 2023 03:43:05 GMT

@vinieme12 Is it not possible to create the row_number field, because I need to do some calculations based on that row numbers.

Re: How to get row count for a group order by date

vinieme12 — Thu, 16 Feb 2023 03:47:32 GMT

I thought we were looking for alternatives since the field creation is slower.

Re: How to get row count for a group order by date

K7pramod — Thu, 16 Feb 2023 03:48:41 GMT

@vinieme12 field creation is needed, but do we have any better ways to perform that actions.