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

[wdchristensen]

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?

[wdchristensen]

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

[wdchristensen]

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. 

[wdchristensen]

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. 

[Channa]

AGGR() is used to replace OverPartition in qlik

[wdchristensen]

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

[wdchristensen]

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

[danilostochi]

in script

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

[wdchristensen]

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

[wdchristensen]

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