How to Unpivot or Explode Variable-Length Lists in Domo to Flatten a Table?
Hi Domo community,
I have a dataset in Domo where the number of postcodes in a cell can vary. Here's an
example of my input data:
| ||||||
|
Network
|
|
State
|
|
Postcode
|
|
---|---|---|---|---|---|---|
| ||||||
|
A
|
|
VIC
|
|
3000,
|
|
| ||||||
|
B
|
|
NSW
|
|
3011,
|
|
I need to transform it into the following format:
| ||||||
|
Network
|
|
State
|
|
Postcode
|
|
---|---|---|---|---|---|---|
| ||||||
|
A
|
|
VIC
|
|
3000
|
|
| ||||||
|
A
|
|
VIC
|
|
3001
|
|
| ||||||
|
A
|
|
VIC
|
|
3002
|
|
| ||||||
|
B
|
|
NSW
|
|
3011
|
|
| ||||||
|
B
|
|
NSW
|
|
3021
|
|
| ||||||
|
B
|
|
NSW
|
|
3001
|
|
| ||||||
|
B
|
|
NSW
|
|
3004
|
|
| ||||||
|
B
|
|
NSW
|
|
3012
|
|
As you can see, the number of postcodes within a cell can vary. How can I achieve this
in Domo, considering the variable-length lists in the "Postcode"
column?
Method we tried
We tried using Split Column tile to split postcode into different columns, but for each
split we need to manually add columns in the configuration. It’s a tedious
task.
We understood that, once we are able to split the postcode into different columns,
we could use unpivot tile to get the desired output.
Any guidance or best practices you can provide for efficiently splitting columns in
my data would be immensely helpful. If you have any recommendations, tips, or
tiles that could streamline this process, I would greatly appreciate your
insights.
Thank you!
Alex
Best Answer
-
You can do that with SQL as suggested or with Magic ETL (somehow I always end up visiting @MarkSnodgrass video on how to build a Word Cloud whenever it comes to a request like this), the logic would remain the same, you just change what the delimiter is that you're splitting by (and might want to add a trim after splitting or a replace for all blank spaces so POs are more or less clean).
2
Answers
-
There isn't really an explode option (csv column → rows) within Magic ETL. You can leverage a MySQL dataflow to help normalize the data. Code like
may help you do this.**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
You can do that with SQL as suggested or with Magic ETL (somehow I always end up visiting @MarkSnodgrass video on how to build a Word Cloud whenever it comes to a request like this), the logic would remain the same, you just change what the delimiter is that you're splitting by (and might want to add a trim after splitting or a replace for all blank spaces so POs are more or less clean).
2 -
Thank you for your assistance! The solution you provided worked perfectly for my data transformation task. I appreciate your help and the clear instructions.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 744 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive