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
@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 |
@vinieme12 @barnabyd @SerhanKaraer Any answers for this problem?
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
@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 .
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)
@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?
these expressions are an alternative to creating the RowNo field at all
@vinieme12 Is it not possible to create the row_number field, because I need to do some calculations based on that row numbers.
I thought we were looking for alternatives since the field creation is slower.
@vinieme12 field creation is needed, but do we have any better ways to perform that actions.