Splitting comma delimited keywords and combining in one column
Use case:
For every account I have a set of comma delimited keywords stored in 1 column (think "SaaS,business,b2b,etc"). I ultimately want to create a list of all unique keywords, then compare that against my dataset to measure frequency ("How many accounts contain "SaaS"?)
My feeble attempt:
I've managed to split the column into 25 Keyword Columns, but am stumped as to how I can now turn these 25 columns back into 1 as a list (1 keyword per row). From there I want to de-dupe and have my clean set of unique keywords.
I wish I could just do a union merge on these 25 columns into 1 (think copy paste each column below each other).
Any ideas?
Best Answer
-
If you've converted the list into multiple columns, you should be able to then take that table of columns into the ETL section and use the Collapse Columns widget. This allows you to specify a column that will 'hold the column labels' and another to hold the values. If you have no values, you can just use a placeholder.
Your result of collapsing those columns should give you the single column you're looking for with multiple rows for each value.
From there you can do a simple select and group by statement.Hope that helps,
Valiant**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.2
Answers
-
If you've converted the list into multiple columns, you should be able to then take that table of columns into the ETL section and use the Collapse Columns widget. This allows you to specify a column that will 'hold the column labels' and another to hold the values. If you have no values, you can just use a placeholder.
Your result of collapsing those columns should give you the single column you're looking for with multiple rows for each value.
From there you can do a simple select and group by statement.Hope that helps,
Valiant**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.2 -
This solved the first step very well! Thank you!
Now I have a single column dataset with individual keywords as rows, nicely trimmed of spaces and de-duplicated ("SaaS", "B2B", etc)
But I'm stumpted on step 2...
I want to go back and compare how many accounts match a given keyword. In Excel I would just do something like search("SaaS", keyword) and count the values. Can I do a left join on a substring (keywords:accounts)?
For example
Account A: SaaS, B2B
Account B: SaaS, B2C
Keyword "SaaS" = 2
Keyword "B2B" = 1
Again, thanks for the help Valiant!
0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 310 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 112 SQL DataFlows
- 650 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 790 Beast Mode
- 78 App Studio
- 43 Variables
- 752 Automate
- 187 Apps
- 477 APIs & Domo Developer
- 71 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 407 Distribute
- 118 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 140 Manage
- 136 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive