topic Re: How to replicate SQL Function ROW_NUMBER() OVER (PARTITION BY ) in QlikSense Tbl in App Development

How to replicate SQL Function ROW_NUMBER() OVER (PARTITION BY ) in QlikSense Tbl

wdchristensen — Sat, 16 Nov 2024 20:43:11 GMT

Example Appointment Table

Field Name Field Description

Member FirstName LastName

Appointment_Dt Date of the Appointment

Type New, Scheduled Follow Up, Unscheduled Follow Up, Term

Confirmed True if the member has confirmed appointment request

I have an existing SSRS report that will provide the next appointment for a member based on a few parameters (@AppointmentType, @AppointmentConfirmed). To accomplish this I am using code similar to (example code attached):

SELECT * FROM

(

SELECT *,

ROW_NUMBER() OVER (PARTITION BY Member ORDER BY Appointment_Dt ASC) QualifyingEventRowNum

FROM Appointments

WHERE [TYPE] IN (@AppointmentType) AND Confirmed=COALESCE(@AppointmentConfirmed, Confirmed) AND

Appointment_Dt >= GETDATE()

) AS QryExample

WHERE QryExample.QualifyingEventRowNum = 1

This allows the users to generate a list of things like:

Next New Appt, Next Confirmed New Appt ,Next Unconfirmed New Appt

Next Unscheduled Follow Up Appt, Next Confirmed Unscheduled Follow Up Appt, Next Unconfirmed Unscheduled Follow Up Appt

Next Scheduled Follow Up Appt, Next Confirmed Scheduled Follow Up Appt, Next Unconfirmed Scheduled Follow Up Appt

Next Term Appt, Next Confirmed Term Appt, Next Unconfirmed Term Appt

** Example image below **

I would like to replicate this functionality in a table in qlik sense so that the data table dynamically reorders the next item based on the current filter selection(s). This essentially excludes performing the calculation in the load script. Can this be accomplished dynamically in Qlik Sense?

Re: How to replicate SQL Function ROW_NUMBER() OVER (PARTITION BY ) in QlikSense Tbl

wdchristensen — Thu, 06 Jun 2019 13:44:53 GMT

I attempted to attach the example code containing the records in the screenshot above only I can't attach the file a .sql or .txt so I posted the code below...

DECLARE @AppointmentType as VARCHAR(50)
DECLARE @AppointmentConfirmed as VARCHAR(5)

SET @AppointmentType='Scheduled Follow Up'
SET @AppointmentConfirmed=NULL

;WITH cteAppointments_01 AS
(

SELECT 'Galileo Galilei 'AS Member, CAST('01/10/2019' as date) AS Appointment_Dt, 'New' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Galileo Galilei 'AS Member, CAST('04/26/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Galileo Galilei 'AS Member, CAST('05/20/2019' as date) AS Appointment_Dt, 'Uncheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Galileo Galilei 'AS Member, CAST('05/24/2019' as date) AS Appointment_Dt, 'Uncheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Galileo Galilei 'AS Member, CAST('07/16/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'FALSE' AS Confirmed UNION ALL
SELECT 'Galileo Galilei 'AS Member, CAST('10/14/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Galileo Galilei 'AS Member, CAST('02/22/2020' as date) AS Appointment_Dt, 'Term' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Ben Bernanke'AS Member, CAST('01/26/2019' as date) AS Appointment_Dt, 'New' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Ben Bernanke'AS Member, CAST('04/28/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Ben Bernanke'AS Member, CAST('05/12/2019' as date) AS Appointment_Dt, 'Uncheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Ben Bernanke'AS Member, CAST('07/24/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Ben Bernanke'AS Member, CAST('07/29/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Ben Bernanke'AS Member, CAST('10/29/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'FALSE' AS Confirmed UNION ALL
SELECT 'Ben Bernanke'AS Member, CAST('02/28/2020' as date) AS Appointment_Dt, 'Term' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Roy Rogers'AS Member, CAST('02/05/2019' as date) AS Appointment_Dt, 'New' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Roy Rogers'AS Member, CAST('05/06/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Roy Rogers'AS Member, CAST('05/20/2019' as date) AS Appointment_Dt, 'Uncheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Roy Rogers'AS Member, CAST('08/05/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'FALSE' AS Confirmed UNION ALL
SELECT 'Roy Rogers'AS Member, CAST('11/05/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'FALSE' AS Confirmed UNION ALL
SELECT 'Roy Rogers'AS Member, CAST('03/08/2020' as date) AS Appointment_Dt, 'Term' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'James Joule'AS Member, CAST('03/12/2019' as date) AS Appointment_Dt, 'New' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'James Joule'AS Member, CAST('06/10/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'James Joule'AS Member, CAST('06/24/2019' as date) AS Appointment_Dt, 'Uncheduled Follow Up' AS Type, 'FALSE' AS Confirmed UNION ALL
SELECT 'James Joule'AS Member, CAST('09/09/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'FALSE' AS Confirmed UNION ALL
SELECT 'James Joule'AS Member, CAST('12/10/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'FALSE' AS Confirmed UNION ALL
SELECT 'James Joule'AS Member, CAST('04/12/2020' as date) AS Appointment_Dt, 'Term' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Vince Vaughn'AS Member, CAST('03/27/2019' as date) AS Appointment_Dt, 'New' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Vince Vaughn'AS Member, CAST('06/25/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Vince Vaughn'AS Member, CAST('07/09/2019' as date) AS Appointment_Dt, 'Uncheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Vince Vaughn'AS Member, CAST('09/24/2019' as date) AS Appointment_Dt, 'Uncheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Vince Vaughn'AS Member, CAST('12/25/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'FALSE' AS Confirmed UNION ALL
SELECT 'Vince Vaughn'AS Member, CAST('04/27/2020' as date) AS Appointment_Dt, 'Term' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Melissa McCarthy'AS Member, CAST('06/27/2019' as date) AS Appointment_Dt, 'New' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Melissa McCarthy'AS Member, CAST('06/29/2019' as date) AS Appointment_Dt, 'Unscheduled Follow Up' AS Type, 'TRUE' AS Confirmed UNION ALL
SELECT 'Melissa McCarthy'AS Member, CAST('09/12/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'FALSE' AS Confirmed UNION ALL
SELECT 'Melissa McCarthy'AS Member, CAST('12/28/2019' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'FALSE' AS Confirmed UNION ALL
SELECT 'Melissa McCarthy'AS Member, CAST('03/29/2020' as date) AS Appointment_Dt, 'Scheduled Follow Up' AS Type, 'FALSE' AS Confirmed UNION ALL
SELECT 'Melissa McCarthy'AS Member, CAST('07/31/2020' as date) AS Appointment_Dt, 'Term' AS Type, 'FALSE' AS Confirmed

)
,
cteAppointments_02 AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Member ORDER BY Appointment_Dt ASC) QualifyingEventRowNum
FROM cteAppointments_01
WHERE [TYPE] IN (@AppointmentType) AND Confirmed=COALESCE(@AppointmentConfirmed, Confirmed) AND Appointment_Dt >= GETDATE()
)

SELECT *
FROM cteAppointments_02
WHERE QualifyingEventRowNum = 1
ORDER BY Member

Re: How to replicate SQL Function ROW_NUMBER() OVER (PARTITION BY ) in QlikSense Tbl

wdchristensen — Fri, 07 Jun 2019 13:53:34 GMT

I was considering using the PEEK function but that is a load script function, not a chart function. I am open to any ideas. Thanks for reading.

Re: How to replicate SQL Function ROW_NUMBER() OVER (PARTITION BY ) in QlikSense Tbl

wdchristensen — Mon, 10 Jun 2019 21:01:13 GMT

I attempted to adapt the Above function as explained below:

https://community.qlik.com/t5/QlikView-Documents/Missing-Manual-Above-and-Below/ta-p/1481948

to approximate the row number function but it doesn't seem like a viable solution since the results change as the sort changes. I have opened up a case with support to see if there is a feature in Qlik Sense that I just have not found on the online help which would address my issue. If I find something, I will post the solution. Thanks for reading.

Re: How to replicate SQL Function ROW_NUMBER() OVER (PARTITION BY ) in QlikSense Tbl

Channa — Tue, 11 Jun 2019 01:07:12 GMT

AGGR() is used to replace OverPartition in qlik

Re: How to replicate SQL Function ROW_NUMBER() OVER (PARTITION BY ) in QlikSense Tbl

wdchristensen — Wed, 19 Jun 2019 21:26:54 GMT

Aggr(RowNo(),(Member,(string,Asc)),(Appointment_Dt,(Numeric,Asc)))

Re: How to replicate SQL Function ROW_NUMBER() OVER (PARTITION BY ) in QlikSense Tbl

wdchristensen — Mon, 24 Jun 2019 16:41:09 GMT

https://community.qlik.com/t5/Qlik-Design-Blog/The-sortable-Aggr-function-is-finally-here/ba-p/1470262

Re: How to replicate SQL Function ROW_NUMBER() OVER (PARTITION BY ) in QlikSense Tbl

danilostochi — Mon, 24 Jun 2019 17:11:53 GMT

in script

NumSum(Peek('cumulative',-1)) + NumSum(field)) as cumulative,

Re: How to replicate SQL Function ROW_NUMBER() OVER (PARTITION BY ) in QlikSense Tbl

wdchristensen — Mon, 24 Jun 2019 19:57:26 GMT

I struggled with this issue for a week and didn’t find much information on my issue so I decided to create an example application for anyone who has this issue in the future. If anyone can improve upon my solution, I would appreciate your suggestions for a DYNAMIC solution (not a static load script solution although I put that in the example as well).

Re: How to replicate SQL Function ROW_NUMBER() OVER (PARTITION BY ) in QlikSense Tbl

wdchristensen — Mon, 24 Jun 2019 19:59:14 GMT

Just in case you don't want to bother with the download the screenshot contains the formula...