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
- 10.5K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 472 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 202 Visualize
- 255 Beast Mode
- 2.1K Charting
- 12 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 247 Distribute
- 63 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 181 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive