Is there an ETL tile to change a row into a column header?

kim_barragan0126
kim_barragan0126 Member
in Magic ETL

I am not sure of the best way, but is there a way for me to take the raw data below and make a table and/or chart?

image.png

Best Answers

  • kim_barragan0126
    kim_barragan0126 Member
    Answer ✓

    Thank you. That did work, but I have lost some of the other columns, such as the start date, timesheet batch etc. Is there another way, or possibly a beast mode to keep the other columns? I especially the start date. If not, no worries, and thank you!

    Happy New Year!

    image.png

  • Data_Devon
    Data_Devon Contributor
    Answer ✓

    Hi Kim_Barragan!

    You can retain those extra columns within the Group By ETL tile that GrantSmith mentioned.

    Just "Add All Columns" in Step 1 of that Group By tile, or select the specific columns that you mentioned. This should retain the columns you need downstream, but also create the group by that you were originally after.

    This should do the trick but let us know if we missed something.

    image.png

    ✅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"!

Answers

  • GrantSmith
    GrantSmith Coach

    You can use the group by tile to group based on the Position Owner and do a count. Then, do another group by tile to do it based on the candidate owner. Once you have those two, you can full outer join them together based on the Owner name. Finally you can fill the count field into a formula tile and to an IFNULL function to see if the count is null then default it to 0.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • kim_barragan0126
    kim_barragan0126 Member
    Answer ✓

    Thank you. That did work, but I have lost some of the other columns, such as the start date, timesheet batch etc. Is there another way, or possibly a beast mode to keep the other columns? I especially the start date. If not, no worries, and thank you!

    Happy New Year!

    image.png

  • Data_Devon
    Data_Devon Contributor
    Answer ✓

    Hi Kim_Barragan!

    You can retain those extra columns within the Group By ETL tile that GrantSmith mentioned.

    Just "Add All Columns" in Step 1 of that Group By tile, or select the specific columns that you mentioned. This should retain the columns you need downstream, but also create the group by that you were originally after.

    This should do the trick but let us know if we missed something.

    image.png

    ✅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"!

  • kim_barragan0126
    kim_barragan0126 Member

    I appreciate all the help!

Categories