How can I take multiple columns and create new rows based on the data?
We input some data through a form that is very limited in how it organizes the data. It currently looks similar to this (these are the column headers):
Date / Manager / State / District / Site / Type of Inspection / 1 - Category of Violation / 1 - Violation / 1 - Plan of Correction / 2 - (same 3 column titles) / 3 - (same) / up to 10
I'd like to be able to use the data in a way where the data resembles this...
Date/Manager/State/District/Site/Type of Inspection / 1- Category / 1 Violation / 1 Plan of Correction
Date/Manager/State/District/Site/Type of Inspection / 2- Category / 2 Violation / 2 Plan of Correction
---and so on
The thought being I can then create cards that categorize all category violations in charts with drill paths to the other data (pie chart of categories of violations). I feel like I should be able to use maybe collapse/uncollapse but my efforts have been comical at best.
Any thoughts? Thanks for the help.
Best Answer
-
Hi Jeremy,
Asuming you are doing this in magic ETL this is how you can accomplish this:
- Add a "Select Columns" box from the "Edit Columns" transforms that selects Date/Manager/State/District/Site/Type of Inspection/1 Violation/ 1 Category of Violation/1 Plan of Correction
- Do this for each set of violation data 1...10 - so the only thing that changes for each select statement is the set of 1... 10 number
- Once you have all of the select statements in place pull in an "Append Rows" transform from the "Combine Data" transforms. and append all of the select columns transforms
- Finally push this into an Output data set
This should get you what you are looking for.
Please let me know if you have any questions.
2
Answers
-
Hi Jeremy - I assume you are trying to use Magic ETL to accomplish this task?
Thanks!
1 -
Correct. I'm trying to do it all within there as I am making some other changes as well.
1 -
Hi Jeremy,
Asuming you are doing this in magic ETL this is how you can accomplish this:
- Add a "Select Columns" box from the "Edit Columns" transforms that selects Date/Manager/State/District/Site/Type of Inspection/1 Violation/ 1 Category of Violation/1 Plan of Correction
- Do this for each set of violation data 1...10 - so the only thing that changes for each select statement is the set of 1... 10 number
- Once you have all of the select statements in place pull in an "Append Rows" transform from the "Combine Data" transforms. and append all of the select columns transforms
- Finally push this into an Output data set
This should get you what you are looking for.
Please let me know if you have any questions.
2 -
I should have thought of that. That's perfect. Thanks so much for the help.
1 -
Ok - try my solution below and you should get what you are after. If you need any clarity please let me know.
0
Categories
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 473 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 209 Visualize
- 257 Beast Mode
- 2.1K Charting
- 12 Variables
- 18 Automate
- 355 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
- 183 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive