How to Unpivot or Explode Variable-Length Lists in Domo to Flatten a Table?

alex_scaria · September 2023

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:

	State


Network	A	State	VIC	Postcode	3000, 3001, 3002

Network	B	State	NSW	Postcode	3011, 3021, 3001, 3004, 3012

I need to transform it into the following format:

	State


Network	A	State	VIC	Postcode	3000

Network	A	State	VIC	Postcode	3001

Network	A	State	VIC	Postcode	3002

Network	B	State	NSW	Postcode	3011

Network	B	State	NSW	Postcode	3021

Network	B	State	NSW	Postcode	3001

Network	B	State	NSW	Postcode	3004

Network	B	State	NSW	Postcode	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

marcel_luthi · September 2023

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).

GrantSmith · September 2023

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 Split (explode) comma delimited column values to rows

may help you do this.

marcel_luthi · September 2023

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).

alex_scaria · September 2023

marcel_luthi

Sep 14, 2023

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).

Thank you for your assistance! The solution you provided worked perfectly for my data transformation task. I appreciate your help and the clear instructions.

Magic ETL

Magic ETL

How to Unpivot or Explode Variable-Length Lists in Domo to Flatten a Table?

Welcome!

Best Answer

Answers

Welcome!

Welcome!

Quick Links

Categories