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.
6 -
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
-
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.
6 -
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 -
Thanks so much everyone especially @MichelleH that worked
0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 470 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 194 Visualize
- 253 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 174 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive