How to get last date
Hi, I am new here!
I have a dataset that has data on multiple rows for the same patient with different encounter dates. I want to only see the unique patient once (so patient ID is the identifier) and show me only the most recent encounter date. Example data:
John Doe, Patient ID 1, encounter date 1/3/2022
John Doe, Patient ID 1, encounter date 3/5/2022
Jane Doe, Patient ID 3, encounter date 10/1/2022
John Smith, Patient ID 2, encounter date 3/1/2022
John Smith, Patient ID 2, encounter date 1/5/2022
John Smith, Patient ID 2, encounter date 6/4/2022
I have tried grouping methods in ETL and that hasn't worked. Can someone please help me?
Best Answers
-
Use a group by tile to group on the name / ID and then take the maximum of your date field.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
@ageibe You can do this with the Group By tile in Magic ETL.
In section #1 (select what columns identify the grouping) put your name and patient Id columns in that section.
In section #2 enter the name you want to give the most recent date column
In section #3 choose the encounter date column and then choose maximum for the aggregation type.
This will give you the most recent encounter date for each patient.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1
Answers
-
Use a group by tile to group on the name / ID and then take the maximum of your date field.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
@ageibe You can do this with the Group By tile in Magic ETL.
In section #1 (select what columns identify the grouping) put your name and patient Id columns in that section.
In section #2 enter the name you want to give the most recent date column
In section #3 choose the encounter date column and then choose maximum for the aggregation type.
This will give you the most recent encounter date for each patient.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.1 -
You guys are awesome! I was doing that but apparently I was missing a step. I was able to get exactly what I needed!
0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 473 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 212 Visualize
- 258 Beast Mode
- 2.1K Charting
- 12 Variables
- 19 Automate
- 356 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 248 Distribute
- 64 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 184 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive