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.

Find more posts tagged with

Magic ETL

Accepted answers

MrMiyagi

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.

All comments

MrMiyagi

Hi Jeremy - I assume you are trying to use Magic ETL to accomplish this task?

Thanks!

Jeremy

Correct. I'm trying to do it all within there as I am making some other changes as well.

MrMiyagi

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.

Jeremy

I should have thought of that. That's perfect. Thanks so much for the help.

MrMiyagi

Ok - try my solution below and you should get what you are after. If you need any clarity please let me know.