FirstSortedValue()

[Ciara]

I know there are lots of posts about this function but I'm tying myself up in knots so decided to post my own question.

I'm not sure this is the correct function to use.

I'm trying to load the first appointment for the morning slot of each resource for each date, and the first appointment for the afternoon slot of each resource.

I've included screenshots of a sample dataset and highlighted the ones I want to load.

As you can see there is no morning appointment for OR04 so for 28th January 2020 I would expect only 6 rows to load.

And for the same date, for the afternoon slot, I would expect 7 rows to load.

Any help is appreciated.

Thanks 

Ciara

[UserID2626]

As you can see there is no morning appointment for OR04 so for 28th January 2020 I would expect only 6 rows to load.

And for the same date, for the afternoon slot, I would expect 7 rows to load.

this 6 & 7 is based on what? time or just an count like 6 , 7,8 ....

[Ciara]

Hi, 

Apologies, i should have clarified.  Yes, based on time.  The morning 'slot' is 0800 to 1300.

Afternoon 'slot' is 1300 to 1800.

Thanks

Ciara

[TimvB]

Hi Ciara,

At first, you need to define the "moring period" and the "afternoon period". For example, the morning period is the period where the StartTimeID is between 0800 and 1200. Then you need to create flag field based on the StartTimeID to identify if a start time is in the morning or afternoon. Also, create a key field (ResourceID & '|' & DateTime & '|' & PeriodFlag as temp_key) which we use later.

After that, you need to find the minimum StartTimeID per DateTime, per moring/afternoon period, and per ResourceID. This can be achieved by the following script:

FirstAppointment_StartTime_map:
Mapping Load
ResourceID & '|' & DateTime & '|' & PeriodFlag as temp_key,
Min(StartTimeID) as FirstAppointment_StartTime
Resident Table1
Group By ResourceID, DateTime, PeriodFlag;

The FirstAppointment_StartTime table holds only records for appoinments that are start first on a Date, in a Resource in a Period. Thus, we can map these records to the original table to create a flag field for the first appointments and delete the temp_key.

Table:
NoConcatenate Load
*,
If(IsNull(ApplyMap('FirstAppointment_StartTime_map', temp_key),null()),'Regular appointment','Initial appointment') as InitialAppointment_Flag
Resident TableFromStep1;

Drop Field temp_key From Table;

Hope it helps! If you struggle with the scripting, please provide a sample dataset in a QVD, Excel or CSV.

[Ciara]

Hi Tim

Thanks so much for your response.

I've been playing around with it since you posted but only coming up with all 'Regular Appointment' flags.

Here's my code

Table:

LOAD
ResourceID,
DateTime,
StartTimeID,
AppointmentID,
If(StartTimeID < '1300',1,2) as PeriodFlag,
ResourceID & '|' & DateTime & '|' & If(StartTimeID < '1300',1,2) as temp_key
FROM [lib://EQvdFilesLive/SCH/E_SchCalendarFileTableAppts.qvd](qvd)
Where ResourceID like 'OR0*';

// NoConcatenate

FirstAppointment_StartTime_map:
Mapping Load
ResourceID & '|' & DateTime & '|' & PeriodFlag as temp_key,
Min(StartTimeID) as FirstAppointment_StartTime
Resident Table
Group By ResourceID, DateTime, PeriodFlag;

NoConcatenate

OR_Stats:
Load *,
If(IsNull(ApplyMap('FirstAppointment_StartTime_map', temp_key , null())), 'Regular appointment', 'Initial appointment') as InitialAppointment_Flag

Resident Table;

// Drop Field temp_key From OR_Stats;

STORE OR_Stats INTO [lib://TQvdFilesLive/CiaraTesting\T_OR_Stats.qvd] (qvd);

Drop Table OR_Stats;

Exit Script;

I've attached an example dataset but not too sure why the InitialAppointment_Flag isnt working.

[TimvB]

Hi Ciara, I found two mistakes in my previous script : (1) the DateTime field should be a timestamp, otherwise Qlik does not match the field in the mapping, and (2) I forgot to include the StartTimeID in the temp_key. 

The script below gives the correct flags:

//=== Load data and create:
//    1. PeriodFlag (1 = Morning, 2 = Afternoon)
//    2. temp_key used to flag the initial appointments per ResourceID, DateTime, PeriodFlag 
Table:
NoConcatenate Load
    ResourceID,
    timestamp(DateTime) as DateTime,
    StartTimeID,
    AppointmentID,
    If(StartTimeID < '1300',1,2) as PeriodFlag,
    ResourceID & '|' & timestamp(DateTime) & '|' & If(StartTimeID < '1300',1,2)  & '|' & StartTimeID as temp_key
FROM [lib://Data/Example Dataset.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where ResourceID like 'OR0*';

//=== Create a mapping of the initial appointments
// Step 1: Find the minumum StartTimeID per ResourceID, DateTime, PeriodFlag
FirstAppointment_StartTime:
NoConcatenate Load
    ResourceID & '|' & timestamp(DateTime) & '|' & PeriodFlag 	as temp_key,
    Min(StartTimeID) 											as FirstAppointment_StartTime
Resident Table
Group By ResourceID, DateTime, PeriodFlag;

// Step 2: Add the minimum StartTimeID to the key to find the match with the initial appointments temp_key
FirstAppointment_map:
Mapping Load
	temp_key & '|' & FirstAppointment_StartTime 	as tempkey,
    'Inital Appointment' 							as Initial_Appointment
Resident FirstAppointment_StartTime;

//=== Flag the appoinment (Inital Appointment | Regular Appointment) 
OR_Stats:
NoConcatenate Load 
	*,
	ApplyMap('FirstAppointment_map', temp_key, 'Regular Appointment') as InitialAppointment_Flag
Resident Table;

//=== Drop the temp_key
Drop Field temp_key From OR_Stats;

STORE OR_Stats INTO [lib://Data/T_OR_Stats.qvd] (qvd);

All the best,

Tim

[Ciara]

Hi Tim

Thank you so much for your help on this.  I've been working on this for a while and I got it to work..........for some appointments!  

I'm getting the Initial Appointment flag for some appointments but not all and I'm struggling to find out what the issue is.

I even changed the period flag times and then removed the flag altogether to see if it would just pick up the first of the day but I can't find a pattern as to why it's picking up the ones it's picking up.

I'm about to tell to customer that it just cant be done 😂

[TimvB]

Hi Ciara, there is probably somewhere a data format (type) issue. Inconsistency in the data format results in having different keys, and thus results in mismatches of the flags. We had the same issue with the DateTime field, where we had to use the function timestamp() to get it to work. For example, often keys such as a AppointmentID start with leading zeros. Qlik treats these keys as integers and thus drops the leading zeros. You then need to use the Text() function so that the leading zeros do not get dropped. You should check the data format of every field and make sure that it is consistent in the whole script. If you still struggle, send me a sample dataset that results in mismatches.

[Ciara · Accepted solution]

Hey Tim

I can't thank you enough for your help with this.

I did change my dataset but with the same information and I got it to work.

Here's what I ended up with.

Table:

LOAD
VisitID,
PatientCaseID,
ActualPrimaryOpUrn,
AccountNumber,
CompleteCharge,
AppointmentID,
ProviderID,
OperationCanReasonID,
OperationRoomID,
OperationDateTime,
Time(OperationDateTime) as OperationTime,
ScheduleOperationDateTime,
OperationType,
SurgeonID,
ApplyMap('Map_ProviderID_Name', SurgeonID, 'Unknown') as SurgeonName,
PatientID,
ScheduledTimeOperation,
If(ScheduledTimeOperation < '1300','Morning','Afternoon') as PeriodFlag,
OperationRoomID & '|' & Timestamp(ScheduleOperationDateTime) & '|' & If(ScheduledTimeOperation < '1300','Morning','Afternoon') & '|' & ScheduledTimeOperation as temp_key
FROM [lib://EQvdFilesLive/SCH/E_SchOrPatCases.qvd](qvd)
Where OperationRoomID like 'OR0*' and CompleteCharge = 'Y';

NoConcatenate

FirstAppointment_StartTime:
Load
OperationRoomID & '|' & ScheduleOperationDateTime & '|' & PeriodFlag as temporary_key,
Min(ScheduledTimeOperation) as FirstAppointment_StartTime
Resident Table
Group By OperationRoomID, ScheduleOperationDateTime, PeriodFlag;

Map_FirstAppointment:
Mapping Load
temporary_key & '|' & FirstAppointment_StartTime as tempkey,
'Initial Appointment' as Initial_Appointment
Resident FirstAppointment_StartTime;

NoConcatenate

OR_Stats:
Load
*,
ApplyMap('Map_FirstAppointment', temp_key, 'Regular Appointment') as InitialAppointment_Flag
Resident Table;

Drop Field temp_key From OR_Stats;

You've taught me a lot of skills just on this script alone so thank you very much.

Ciara