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

[K7pramod]

@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

[K7pramod]

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

[vinieme12]

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

[K7pramod]

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

[vinieme12]

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)

[K7pramod]

@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?

[vinieme12]

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

[K7pramod]

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

[vinieme12]

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

[K7pramod]

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