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,
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

 

 

 

 

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    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).

Answers