unpivoting data
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.
Best 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!**0
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.” -Superman0 -
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!**0 -
It's a recent addition and not yet mentioned in the docs, but Magic's
GROUP_CONCAT
does support theDISTINCT
keyword now.
A Group By tile with grouping columnAccount
and the formula aggregatesGROUP_CONCAT(DISTINCT Category SEPARATOR ', ')
andMIN(Date)
might be what you're looking for.Randall Oveson <randall.oveson@domo.com>
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive