unpivoting data

mroker

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.

GrantSmith

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.

ST_Superman

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

if you do that by account it should work for you

GrantSmith

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.

rco

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.