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

slewis1
slewis1 Member
edited March 2023 in Datasets

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.

Tagged:

Best Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    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"

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    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"
  • Thanks so much everyone especially @MichelleH that worked

  • 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.