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