How to build a filter for only the most recent value of a column in a data set to show.
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.
Best Answers
-
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.
5 -
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.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"3
Answers
-
Thanks so much everyone especially @MichelleH that worked
0
Categories
- 7.4K All Categories
- 13 Getting Started in the Community
- 151 Beastmode & Analytics
- 1.8K Data Platform & Data Science
- 54 Domo Everywhere
- 2K Charting
- 1K Ideas Exchange
- 905 Connectors
- 239 Workbench
- 343 APIs
- 77 Apps
- 19 Governance & Productivity
- 237 Use Cases & Best Practices
- 50 News
- 473 Onboarding
- 573 日本支部