How can I take multiple columns and create new rows based on the data?

Jeremy

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.

MrMiyagi

Hi Jeremy,

 

Asuming you are doing this in magic ETL this is how you can accomplish this:

 

1. 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
2. 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
3. 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
4. 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.

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:

 

1. 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
2. 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
3. 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
4. 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.