Normalize data in Domo.

collin

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

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

You can easily do this in the Magic ETL dataflow. 

 

First get your input dataset:

 

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

 

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

 

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

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

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

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

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 [email protected]. 

John

You can easily do this in the Magic ETL dataflow. 

 

First get your input dataset:

 

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

 

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

 

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"

canioacaputo

Agree with SQL in Redshift.  That's going to be the way to go