# Alteryx Designer Discussions

The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!

## Filtering Datetime with logic involving another column

Highlighted
6 - Meteoroid

I'm hoping somebody can help me with this logic issue.  I have 2 different things I'm trying to do.  I'm trying to determine the datetimestamp for the end of an appointment and trying to filter for datetimes prior to that appointment.  The dataset has multiple dates, locations, patients and room statuses.  So grouping is an issue.

Task 1:  Determine end of appointment timestamp:  The dataset is grouped and ordered ascending by date, patient, location,  appointments (rooms), roomtime (datetime).   I'm concerned about calculating timestamps in relation to this main appointment:

Want to filter for all of the timestamps that occur after this room timestamp that are Room = "CHECK-OUT" OR "DISCHARGED" and sample the first record (or minimum) to determine when they left the actual MAIN APPT room.

Task 2: Determine which appointments immediately before this main appointment. (i.e. appointments that occur before )  Want to filter for all the timestamps that occur prior to this timestamp to determine which appointment is immediately before that could cause a delay.  I can do my own analysis on this dataset, but wasn’t sure how to filter it based on the grouping of date, location, patient.

I've attached the sample data if anybody can help!

Highlighted
12 - Quasar

Hi, @kherty

Not sure what you want to do with the filtered results (for example, stitch them back onto the main data set).

Still, try the below.

The Workflow is attached.

Highlighted
6 - Meteoroid

@RobertOdera  Ha!  Spoke too soon.  I wasn't sure how to utilize the Multi-Row Formula tool like you did using the expression:

IF [Date]=[Row-1:Date] AND
[sPatientNumber]=[Row-1:sPatientNumber] AND
[sLocation]=[Row-1:sLocation] AND
CONTAINS([Room],'CHECK-OUT') THEN 1

ELSEIF [Date]=[Row-1:Date] AND
[sPatientNumber]=[Row-1:sPatientNumber] AND
[sLocation]=[Row-1:sLocation] AND
CONTAINS([Room],'DISCHARGED') THEN 1

ELSE 0
ENDIF

However, I still need to find only those timestamps that are "after" the main appointment time.  The Check-out and Discharge is only when they leave the facility.  They may have another appointment (room) after the main appointment?  Any idea how to incorporate datetime into this to only allow the datetimes that are > than the main appointment time?

Highlighted
12 - Quasar

Sure, @kherty

I'll alter the flow to do that for you.

Cheers!

Highlighted
12 - Quasar

Hi, @kherty

Step1: Set Start, where Start tag CONTAINS([Room],'READY MAIN APPT'

Not sure from your verbiage, if this needs to be at [Room] = 'MAIN APPT' versus [Room] ='READY MAIN APPT'

You can make the change based on what you need as a start point

Also Set End, where End tag CONTAINS([Room],'CHECK-OUT') or CONTAINS([Room],'DISCHARGED')

You will have two End values for some, from which we will extract the min per your earlier request

Step2: Get appropriate End (min Date Time value)

Used Sort Tool + Sample Tool to identify these + Union + Sort Tool to stitch back into the data set and re-sort accordingly.

Step3: Filter to desired outputs

Labels