Combine multiple distinct rows values in one cell
Hi,
I am trying to combine the distinct value from multiple rows in a column with Beast mode.
Here is an example of my file:
Field A | Field B | FieldC |
A | 1 | 111 |
A | 2 | 222 |
A | 3 | 333 |
A | 4 | 333 |
B | 5 | 111 |
B | 6 | 444 |
C | 7 | 555 |
I would like to have:
Field A | Field B |
A | 111, 222, 333 |
B | 111, 444 |
C | 555 |
In this case I have a concat but I would also need to do a distinct sum potentially.
Any ideas on how I can achieve this.
Thanks.
Comments
-
I think for both of those you'd need to do it in an ETL process.
For the concat, the Beast Mode to contact values, you'd need to specify them out, and there isn't funcationlity within a BM to go back and handle rows 1 by 1 and add them in there (that I can think of).
For the distinct sum, you'd have to be able to distinguish the uniqueness prior to making it to the card for a BM. You only have the option for a disctinct(count( in BM.
What I'd probably do, is for the concat...I've never had that use-case in domo, but you may be able to accomplish it with mysql and create a stored proc and do some cursor logic to get that to work. (I may be way over complicating that so if there is an easier way I'm following this to find out).
For the distinct sum, you'd have to maybe create another column in ETL for the value to be a string and then another for it to be a numberic. Then do a remove duplicates ...and group from there doing a sum of the numeric column.
Good question on this so I'm following to see if anyone else has a better answer...I hope that helps.
Matt
0 -
Thanks NewsomSolutions.
Not sure to understand your option with mysql and storeproc..
Anyone else?
0 -
Is this still an issue? I'm relatively new to Domo and amazed this isn't easier. It could be achieved in the 'Group By' tile.
There's already 'Combine strings separated by ,'
Could @domo add 'Combine unique strings separated by ,' ?
0 -
Use the "Add Formula" button in Group By to write something like this:
GROUP_CONCAT(DISTINCT FieldC SEPARATOR ', ')
Your "columns which identify the grouping" would just be "Field A".
Charlie, yes, we certainly can and should add an option to that drop-down to "Combine unique strings separated by ,". Generally, features become available first in the formula editor and later in the mouse-driven interface. I'll try to put in a word for trickling this particular feature down sooner rather than later, since it seems to come up a lot on these forums.Randall Oveson <randall.oveson@domo.com>
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 709 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive