How to build a filter for only the most recent value of a column in a data set to show.

slewis1

I've got a dataset of patients for my hospital and each patient has a unique medical record number. The dataset has multiple visits from the same patients and each visit gets a unique account number. Each visit there is also a calculated readmission risk score. I'm trying to build a filter either in the ETL or on the Domo card that would only show the patient's most recent visit readmission risk score. How would I go about doing this?

I may also want to do another card where it separates out patients and then lists all of their readmission scores and was thinking of a table for that.

MichelleH

Hi @slewis1 I would suggest using a Rank and Window tile in the ETL to add a row number column, ordered by visit date (descending) and partitioned by patient record number. You can then filter to rows where this row number equals 1 to identify the most recent visit.

More on the Rank and Window tile: https://domohelp.domo.com/hc/en-us/articles/360044876094-Magic-ETL-Tiles-Aggregate#3.

jaeW_at_Onyx

only addendum I'd add to @MichelleH 's post would be to not filter out rows. just add your row_number for nth-most-recent visit.

in one card you can filter on "nth-most-recent-visit = 1" and on the other card just filter by patient.

MichelleH

Hi @slewis1 I would suggest using a Rank and Window tile in the ETL to add a row number column, ordered by visit date (descending) and partitioned by patient record number. You can then filter to rows where this row number equals 1 to identify the most recent visit.

More on the Rank and Window tile: https://domohelp.domo.com/hc/en-us/articles/360044876094-Magic-ETL-Tiles-Aggregate#3.

jaeW_at_Onyx

only addendum I'd add to @MichelleH 's post would be to not filter out rows. just add your row_number for nth-most-recent visit.

in one card you can filter on "nth-most-recent-visit = 1" and on the other card just filter by patient.

slewis1

Thanks so much everyone especially @MichelleH that worked

ben111

Hey everyone,

I totally get where you're coming from, lewis1. Dealing with datasets, especially in a hospital setting, can be quite a puzzle. Your situation reminds me of a project I worked on last year. It's fantastic that you're aiming to focus on the most recent visit's readmission risk score – that's where the real insights lie.

What you need here is a two-step approach. First, in your ETL process, sort the dataset for each patient by visit date in descending order. This way, the most recent visits will be at the top. Then, when creating your Domo card, utilize a filter that only pulls in the top row for each patient (their latest visit) based on the unique medical record number.

For your other card idea, a table sounds perfect. Just list the patients and their corresponding readmission scores. Again, using the sorted dataset, you can achieve this by displaying the first row for each patient.