ETL: Add an auto-uncollapse (pivot) feature

DataSquirrel

Domo has an ETL Edit Columns action called "uncollapse columns", what I'd call a "pivot". This feature works, but it requires manual configuration. In cases where the DataSet includes a clear category (split/break/name) column and a clear value column, please add an auto-pivot option. Instead of having to manually describe dozens of specific labels (category/split/break/name) values, have Domo figure it out. You would still want the existing uncollapse feature, but the auto-uncollapse would simplify pivoting name-value-pair data.

 

More details. Starting from the existing feature, here's the picture from the docs. Starting with this input:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Split the name-value pairs out into distinct columns/facts like this:

 

 

 

 

 

 

 

 

 

 

There are five steps in this process:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This process works, but it is a non-starter if you've got a lot of categories or dynamic categories. Please add an ETL feature that automates this process using only three configuration settings: Label, Key, Value. These are the three basic elements any pivot requires, and you've got them set up beautifully in Domo already. (It's very nice that you anticipate keys that are defined by combining several columns.) 

 

Notice that all of the configuration in the bottom three steps can be automated completely. The label value is always from the Product column, the identifying key isalways from the Month column and the value isalways from the Sales column. Here's a mock-up of the auto-pivot setup:

 

 

 

 

 

 

 

 

 

 

 

 

That's it. Domo then has to:

1. Find the distinct values in the first (label) column. These define the pivot columns.
2. Find the distinct rows in the second (key) column(s). These define the pivot rows.
3. Copy the values in the third (value) column into the column and row they match.

I assume that if you do this that you should do something similar for unpivot, but I havne't needed it or thought it through.

 

Thank you.

DataSquirrel

P.S. As it stands, we aren't able to use Domo's pivot, which is a shame. Instead, we have to write custom scripts or use the Postgres crosstab extension.

dsutclif

DOMO Please develop this!

It would be so good to have. 

DS, great idea. Great job explaining it so clearly.

DaniBoy

Thanks for posting and commenting @DataSquirrel and @dsutclif!

Please vote this one up so we can get it assigned to Engineering for review.

 

Regards,

Dani

user06300

Well done! This is a no-brainer and I'm surprised that this is still not developed!

---

@DataSquirrel wrote:

Domo has an ETL Edit Columns action called "uncollapse columns", what I'd call a "pivot". This feature works, but it requires manual configuration. In cases where the DataSet includes a clear category (split/break/name) column and a clear value column, please add an auto-pivot option. Instead of having to manually describe dozens of specific labels (category/split/break/name) values, have Domo figure it out. You would still want the existing uncollapse feature, but the auto-uncollapse would simplify pivoting name-value-pair data.

 

More details. Starting from the existing feature, here's the picture from the docs. Starting with this input:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Split the name-value pairs out into distinct columns/facts like this:

 

 

 

 

 

 

 

 

 

 

There are five steps in this process:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This process works, but it is a non-starter if you've got a lot of categories or dynamic categories. Please add an ETL feature that automates this process using only three configuration settings: Label, Key, Value. These are the three basic elements any pivot requires, and you've got them set up beautifully in Domo already. (It's very nice that you anticipate keys that are defined by combining several columns.) 

 

Notice that all of the configuration in the bottom three steps can be automated completely. The label value is always from the Product column, the identifying key isalways from the Month column and the value isalways from the Sales column. Here's a mock-up of the auto-pivot setup:

 

 

 

 

 

 

 

 

 

 

 

 

That's it. Domo then has to:

1. Find the distinct values in the first (label) column. These define the pivot columns.
2. Find the distinct rows in the second (key) column(s). These define the pivot rows.
3. Copy the values in the third (value) column into the column and row they match.

I assume that if you do this that you should do something similar for unpivot, but I havne't needed it or thought it through.

 

Thank you.

---