Normalize data in Domo.
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?
Best Answers
-
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?-----
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"1 -
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"2
Answers
-
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?-----
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"1 -
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.
0 -
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.
Broadway + Data0 -
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 > 0This 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.-----
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"2 -
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"2 -
Agree with SQL in Redshift. That's going to be the way to go
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive