Determining the most recent referral prior to an appointment date for each patient

[AJCT100]

Hello People!

I am not new to Alteryx but in the absence of SQL server which I used to use for a lot of the heavy lifting, I am now using Alteryx exclusively. Often, in the clinical environment, I need to ascertain what was the most recent referral a patient had to a specialty prior to a particular event or episode. In SQL I would do something like:

select

PatientID,

(Select max(ReferralDate) from tblReferrals ref where ref.PatientID = att.PatientID and ref.ReferralDate < att.AttendanceDate) as LastReferralDate

from tblAttendances att

Is there a way I can achieve this with Alteryx, and I hope I have made myself clear! :) 

Thank you!

[DataNath]

Unfortunately I’m not massively comfortable with SQL yet. However, from the scenario you have given it sounds like you could just use a filter tool to filter out dates prior to the event/episode, and then use a summarise tool to group by patient and find the max referral date. Can’t build out a flow unfortunately as I’m not at my laptop and there’s no sample to go off but hope this helps! Perhaps someone with better SQL knowledge may see that this doesn’t work and can provide a better solution!

[AJCT100]

Hi DataNath

Thank you for the reply! The only problem is (and I didn't explain it too clearly!) is that a patient can have multiple events so I need to understand the closest referral date to any of the events (events could be an outpatient appointment, or an emergency department episode etc..).  

[DataNath]

What schema does your data take? Are you able to provide a screenshot or even sample with dummy data (assuming the original is sensitive)? If the shape allows then you could just use DateDiff calculations in the formula tool and return the record with the minimum, though if not there’ll likely be another alternative!

[rfoster7]

I am attaching a workflow I built to demo to my team how to do a complex join where you are trying to get a date from table2 between two dates in table1. The mechanics are the same for what you are trying to do. You will to join table 1 (tblAttendances) to table 2 (tblReferrals) on PatientID = PatientID. You will know that that is a partial cartesian join where you will get one row for each attendance for each referral. Something your SQL SERVER SQL engineer brain will balk at. FEAR NOT! We shall use to further tool to resolve that data multiplication

From there, you put a filter tool after the join tool for tblReferral.ReferralDate < tblAttendance.AttendanceDate. That will get rid of a bunch of the data, but still won't give you exactly what you want. 

For that you add a final Group By tool to get the recrods from TblAttendance and the Max of tblReferral.ReferralDate. That will be your "latest referral before the particual attendance" that you are trying for. 

so use the middle (using join and filter) example from this workflow as your guide. And add your group by after the filter to get what you want. 

You will also notice a couple other options in this workflow. One uses a 3rd party 'advanced join' tool that is available on the community. It may meet your needs easier, but I'm not sure. 

And there's the fourth option which is using R tool to pump the two record sets into R where you can write some straight SQL to do the join. You might like that. one. 

Hope this helps.