unpivoting data

mroker
mroker Member
edited April 2023 in Magic ETL

I have data this way:

Account | Category | Date

12 | Phone | 01/01/22

12 | TV | 01/01/22

13 | Phone | 01/02/22

12 | TV | 01/01/22

I would like to figure out on each date the counts for each subcategory. First i would want the data pivoted this way:

Account | Category | Date

12 | Phone, TV | 01/01/22

13 | Phone | 01/02/22

Or to where the categories would be split into columns.

Is this possible? Then I could make a formulae to say which customers have what services on a particular date.

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    group_concat won't remove duplicates like in your example. If you need to remove duplicates you'd need to feed it through a remove duplicates tile first, then do a group by based on account id and date and select the "combine columns with ," option.

    I typically try to avoid combining values into a CSV list though as it's much hard to filter the records for a specific service.

    Another option is to feed it through an add constant tile to add another column and call it "Has Service". Feed that into a pivot tile to pivot your data where you have the account and date as columns, use the service as your value to pivot, and the new Has Service as your value. This will give you a 1 if they have that service on the given day, allowing for easier filtering. You could even use a formula tile to convert the 1 to a Yes and a null to a No.

    If you don't want multiple filters for each of the services but want to keep it as a single filter for the service types then just keep your dataset as is.

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

Answers

  • I believe you can use the group by tile to use group_concat()

    if you do that by account it should work for you


    “There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
  • GrantSmith
    GrantSmith Coach
    Answer ✓

    group_concat won't remove duplicates like in your example. If you need to remove duplicates you'd need to feed it through a remove duplicates tile first, then do a group by based on account id and date and select the "combine columns with ," option.

    I typically try to avoid combining values into a CSV list though as it's much hard to filter the records for a specific service.

    Another option is to feed it through an add constant tile to add another column and call it "Has Service". Feed that into a pivot tile to pivot your data where you have the account and date as columns, use the service as your value to pivot, and the new Has Service as your value. This will give you a 1 if they have that service on the given day, allowing for easier filtering. You could even use a formula tile to convert the 1 to a Yes and a null to a No.

    If you don't want multiple filters for each of the services but want to keep it as a single filter for the service types then just keep your dataset as is.

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

    It's a recent addition and not yet mentioned in the docs, but Magic's GROUP_CONCAT does support the DISTINCT keyword now.

    A Group By tile with grouping column Account and the formula aggregates GROUP_CONCAT(DISTINCT Category SEPARATOR ', ') and MIN(Date) might be what you're looking for.