How to look at multiple strings in one column

ageibe

Hi, created a Case like below:

case

when `All File Dates` >= `EncounterDate` then 'Probable'

else 'Obtain Op Note'

end

This doesn't work to full capacity because the dates in the "All File Dates" column are in no real order. See below highlighted row. The output for Op note on file should say 'Probable' because the encounter date is less than 2022-08-04 but the case statement apparently is only reading the first date.

In ETL, I had converted the date range to text so I could group them together in 1 row.

Any idea how I can compare the encounter date to the all file dates column and have it look at all dates?

GrantSmith

You can use a Group By tile in your ETL for each encounter date and location to get the min or max date instead of making it a comma separated list of strings. You can then compare to the maximum date instead of all the dates. You'll need to join your group back to your original dataset based on your grouping fields to put the date into your dataset for comparison.

ageibe

So unfortunately, there could be multiple encounter dates for 1 patient. So if the encounter dates were 1/1/22, 3/3/22, and 5/4/22, then I would need each of those rows (each encounter on separate row) to look at all of the file received dates in the string to make the search work. Any other thoughts?

GrantSmith

So are you just looking to see if the most recent file received date comes after the encounter date for each patient?

ageibe

I wish it was that simple. There should be a file associated with each encounter, but to identify which encounter is missing the file, we need to compare the file received dates with the encounter dates. The raw dataset has the files listed individually per row with a patient ID key. I combined that data with my encounters data.

If there is an easier way to cross reference the file received dates as rows instead of grouping them, I would be willing to try that too.