Combining Row Data

EWold
EWold Member
in Magic ETL

Hi everyone!

I've asked this question before and was advised to use a pivot table to combine two rows into one, but that would create new columns which I don't want so I'm going to see if anyone else has a solution. I have a dataset that I built that tracks activity by students at a University and I need to combine two rows together without creating more columns. Currently, one row is for the class and the other is for the lab for the class. The course and lab for the course have different course codes so the only thing the courses have in common is similar names and course numbers.

Is there a way I can combine these courses together while still having all of the students listed? Below is an example of what my output looks like at the moment and then what I would like it to look like.

image.png
image.png

Please let me know if I need to provide any more information that would help.


Thanks,

Erik

Comments

  • tejusarora
    tejusarora Member

    You can use a group_concat formula within the group tile in ETL 2.0:


    Add the common data to the identifying column lists (such as user name, firstName, LastName)

    and then add these to the aggregated column by formula:

    MasterCourse: group_concat(MasterCourses_courseName Separator ' - ')

    LastLogin: Max(lastLogin)

    MasterCourses_Code: group_concat(MasterCourses_Code Separator ' / ')


    This should work.


    Best, TJ

  • GrantSmith
    GrantSmith Coach

    You could utilize a Magic ETL to split out the lab classes and the lecture class, calculate the lecture class out from the lab class course code, join the two datasets together based on the lecture code and username then use a combine column tile to join your two columns together (alternatively you can use a formula tile with CONCAT for more control on how your resulting value is formatted) then use a select columns tile to remove any extra columns you may not want in your resulting dataset.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    edited May 2022

    @EWold what's the problem you're trying to solve?

    It doesn't make sense to me (yet) to combine a lecture and lab (without adding columns) it seems like you'd be assuming that a student cannot attend a lecture and NOT a lab or vice versa. or perhaps get registered for different sections that usually aren't paired together, or fail one quarter and have to retake lab, or have a data entry problem in the system ...

    for most analytic cases i can envision, it'd make the most sense to have the data continue to be on separate rows, then do your aggregation in analyzer.


    @GrantSmith 's solution would work, but you'd need some version of a FULL OUTER JOIN to accommodate for the above mentioned circustances.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • EWold
    EWold Member

    @jaeW_at_Onyx the class and lab have different org unit IDs but they can't take one without the other which is why I want to combine them. I'm fine doing the aggregation in the analyzer, but I'm unsure as to what those steps would be.

Categories