Normalize data in Domo.

collin · April 2015

I have a data source that looks like this:

Type BusinessUnit Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec

Sale US 4 6 7 2 1 3 4 6 8 2 1 7

I want it to look like this:

Type BusinessUnit Month Amount

Sale US Jan 4

Sale US Feb 6

Sale US Mar 7

etc etc

an I do this once the Data source is in Domo maybe via Data flows if no other easy way?

letsdothis · April 2015

If there is no possible way to normalize the data at the datasource before it comes into Domo, then a dataflow is your best choice. However, if the data continues to grow as time goes on and new months are added to the table as columns, then each month the dataflow would need to be adjusted. In order to normalize this with a dataflow, SQL pivoting is needed.

What type of datasource is the file?

John · October 2015

You can easily do this in the Magic ETL dataflow.

First get your input dataset:

Screen Shot 2015-10-05 at 1.46.02 PM.png

Then use the 'Collapse Columns' action and configure it in the manner shown below:

Screen Shot 2015-10-05 at 1.47.42 PM.png

You can then preview the output, which should look like this:

Screen Shot 2015-10-05 at 1.47.29 PM.png

You will then need to add an output datasource and you should be good to go.

Hope this helps.

-----
I work for Domo.
**Say "Thanks" by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as "Accepted Solution"

letsdothis · April 2015

If there is no possible way to normalize the data at the datasource before it comes into Domo, then a dataflow is your best choice. However, if the data continues to grow as time goes on and new months are added to the table as columns, then each month the dataflow would need to be adjusted. In order to normalize this with a dataflow, SQL pivoting is needed.

What type of datasource is the file?

collin · April 2015

It's a spreadsheet maintained by Finance. For this one I can fix however I am sure that other datsources will have this problem and was hoping there was an easy way like some other ETL tools provide.

RobynLinden · April 2015

I have this need as well, and have been transforming my data source within excel. Really hoping the new Magic tool will make this process easier. I just got DataFlow turned on in my instance, but now need to learn how to write the code properly.

letsdothis · April 2015

Until ETL is available, the current workaround is to do it in Dataflow.

I've seen this issue before, so here is some sample code to get you started.

NON-NORMALIZED TABLE: 

|PK|saleDate  |saleAmount|reno   |saltLakeCity|lasVegas|denver|
===============================================================
|01|2014-1-1  |53000.00  | 1     | NULL       | NULL   | NULL |
|02|2014-2-15 |47000.00  | NULL  | 2          | NULL   | NULL |
|03|2014-3-24 |68000.00  | NULL  | NULL       | NULL   | 3    |
|04|2014-4-1  |72000.00  | NULL  | NULL       | 4      | NULL |
|05|2014-5-6  |27000.00  | 2     | NULL       | NULL   | NULL |

AWESOME NORMALIZED QUERY:

|saleDate  |saleAmount|City           | saleQuantity |
======================================================
|2014-01-01|53000.00  |Reno           | 1            |
|2014-02-15|47000.00  |Salt Lake City | 2            |
|2014-03-24|68000.00  |Denver         | 3            |
|2014-04-01|72000.00  |Las Vegas      | 4            |
|2014-05-06|27000.00  |Reno           | 2            |

To pivot the table, this is the SQL:

SELECT salesTable.saleDate, salesTable.saleAmount, 'Reno' AS city, salesTable.reno AS saleQuantity
FROM salesTable
WHERE salesTable.reno > 0 

UNION

SELECT salesTable.saleDate, salesTable.saleAmount, 'Salt Lake City' AS city, salesTable.saltLakeCity AS saleQuantity
FROM salesTable
WHERE salesTable.saltLakeCity > 0

UNION

SELECT salesTable.saleDate, salesTable.saleAmount, 'Las Vegas' AS city, salesTable.lasVegas AS saleQuantity
FROM salesTable
WHERE salesTable.lasVegas > 0

UNION

SELECT salesTable.saleDate, salesTable.saleAmount, 'Denver' AS city, salesTable.denver AS saleQuantity
FROM salesTable
WHERE salesTable.denver > 0

This will pull the sales results from each location into a column, add the name of the city in a new column and break it down in a normalized format that can be digested in Domo.

However, If I opened a new sales office and added a new column to the list, I will need to add a new UNION block to the SQL to explicitly account for that.

If you have any questions or needs for dataflow assistance with this, please open up a ticket with support at support@domo.com.

John · October 2015