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

  • MrMiyagi
    Answer ✓

    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.


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



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

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

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

This discussion has been closed.