Pivot/Group By - Make Multiple rows into one row and add columns
Hi Community - thanks as always for the consistent great help.
I'm attempting to Group/Pivot/Join/Append (not sure exactly what the methodology will be) multiple rows of Patients into single rows with additional columns.
Right now, each patient event is on it's own row. So, a patient seen on Monday and then also on Tuesday will have two separate rows.
I want all of these extra rows to be added as new columns.
My purpose is to eventually find the nurse that did an evaluation prior to a surgery. If each patient is on it's own row, then I can filter down to "Surgery" rows and see which nurse did the Evaluation.
The screenshot above shows my intent, even though it's woefully simplified - each patient will have 20+ rows, with 100s of columns each… The theory/logic will be the same though.
✅Did this solve your problem? Accept it as a solution!
❤️Did you love this answer? Mark it as "Awesome"!
👍Do you agree with this process? Click "Agree"!
Best Answers
-
@Data_Devon - yes, unfortunately that is a limitation of the Pivot tile. There is no dynamic generation. Keep in mind that you wouldn't have to code every single patient ID, just the appointment types. The patient ID would be your group (row) identifier. Not sure how many appointment types that you have. If that's too cumbersome as well, below is a pathway to achieve what you want.
One workaround is to use the python tile instead and pandas Pivot() function.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0 -
@Data_Devon How strict are the requirements for how the data is formatted? I often find that "pivoting" in card form is much more flexible than hard-coding columns into the dataset.
For your use case, it may make more sense to create a separate branch of your ETL to filter or aggregate your data so you have a single row per Patient ID with the Nurse details. Then you can join that back to the original branch by Patient ID so all rows for that patient contain a column with the Evaluation nurse.
0
Answers
-
@Data_Devon I’d recommend looking at and using MagicETL pivot roles. Here is a link to the documentation. Feel free to reach out if you have any questions.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0 -
Thanks @david_cunningham !
That's exactly what I was thinking too, but the Pivot tile in Magic ETL forces you to hard-code each patient ID. (see Screenshot below)
With 10,000+ patients, that is not possible.
Does that make sense? w
✅Did this solve your problem? Accept it as a solution!
❤️Did you love this answer? Mark it as "Awesome"!
👍Do you agree with this process? Click "Agree"!
0 -
@Data_Devon - yes, unfortunately that is a limitation of the Pivot tile. There is no dynamic generation. Keep in mind that you wouldn't have to code every single patient ID, just the appointment types. The patient ID would be your group (row) identifier. Not sure how many appointment types that you have. If that's too cumbersome as well, below is a pathway to achieve what you want.
One workaround is to use the python tile instead and pandas Pivot() function.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0 -
@Data_Devon How strict are the requirements for how the data is formatted? I often find that "pivoting" in card form is much more flexible than hard-coding columns into the dataset.
For your use case, it may make more sense to create a separate branch of your ETL to filter or aggregate your data so you have a single row per Patient ID with the Nurse details. Then you can join that back to the original branch by Patient ID so all rows for that patient contain a column with the Evaluation nurse.
0 -
@MichelleH thanks for extra input.
That is what I ended up doing - I filteredd out Evals and then I filtered out Surgeries, and then I joined them back in on eachother to get an output that had both on one line.
✅Did this solve your problem? Accept it as a solution!
❤️Did you love this answer? Mark it as "Awesome"!
👍Do you agree with this process? Click "Agree"!
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 621 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 744 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 124 Manage
- 121 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive