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

  • letsdothis
    letsdothis Domo Employee
    Answer ✓

    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"
  • John
    John Domo Employee
    Answer ✓

    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"

Answers

  • letsdothis
    letsdothis Domo Employee
    Answer ✓

    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"
  • 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
    RobynLinden Contributor

    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 + Data
  • letsdothis
    letsdothis Domo Employee

    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. 

    -----
    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"
  • John
    John Domo Employee
    Answer ✓

    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"

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