Skip to content

    • Magic ETL

    Magic ETL

    Row Combine

    Member
    in Magic ETL

    Hi everyone,

    I've asked a similar question before, but I'm struggling to understand how to best combine multiple rows into 1 while still combining the data from the existing columns using a ,. I have a list of students who are enrolled in a course and they share an OrgUnitIDand then have different times when they have logged in and completed assignments.

    How do I combine these students who share an OrgUnitID into one row using Magic ETL? I've looked all over the resources I can find and it seems that there have been different ways to do this in the last few years.

    So far I've tried to use the Group By tile, but I don't want to create new columns and from what I understand the group_concant function is no longer an option.

    Here is an example of what I'm trying to accomplish.

    image.png


    I would like to combine all of the rows in the screenshot into one and then only display the most recent time for DP_DatePosted and all of the columns to the right of it.

    Any help with the steps I could take to accomplish this would be greatly appreciated!

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Answers

    • Coach

      How are you wanting QA_TimeStarted and QA_TimeCompleted and CA_DateAccess handled? Most recent time? Drop them?

      It sounds like you just want to us a Group By tile on the UE_OrgUnitID, OU_MasterCourses_CourseName, OU_CounselsA fields and take the MAX of the DP_DatePosted field.

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

      @GrantSmith Thank you for always being on the ball with questions!

      Yes, I want the most recent times for QA_TimeStarted QA_TimeCompleted and CA_DateAccess.

      So would I add all of the columns I want to bring in or just these 3?

      image.png

      Also, how would the second part for the new aggregated column look? I wasn't trying to create a new column so this part confuses me.

      image.png


    • Domo Employee

      @EWold - You don't need to include UE_OrgUnitID in the second part of the ETL tile.

      You only want to aggregate the three time values that you listed earlier.

      QA_TimeStarted -> Maximum

      QA_TimeCompleted -> Maximum

      CA_DateAccess -> Maximum


      This will take your data and provide you with a single row of data for each unique grouping of

      • UE_OrgUnitID
      • OU_MasterCourses_CourseName
      • OU_CourseIsActive

      Each row will contain the most recent value for each of the times listed above.

    • Member

      Thank you, everyone! I now understand how the Group By tile works and was able to get my report completed.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories