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

alex_scaria

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, 3001, 3002
	B		NSW		3011, 3021, 3001, 3004, 3012

 

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

 

 

 

 

marcel_luthi

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

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 https://stackoverflow.com/questions/17269444/split-explode-comma-delimited-column-values-to-rows may help you do this.

marcel_luthi

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

https://community-forums.domo.com/main/discussion/comment/76799#Comment_76799

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