Average Data before Combining in ETL

I'm trying to create an end result of comparing Rain to Sales by Day.

 

I pulled Rain data from the NOAA connector for the entire city, which ends up breaking it out into individual Stations across the city. The issue I'm running into is that when I combine this with Sales data for the city, Domo adds the total city Sales to each Station in the city individually (by row). This means that it basically multiplies the amount of sales by the amount of Stations in the city.

 

Is there a way in ETL for me to take the average Rain data across all of the Stations for each individual Date and then combine it with the singular Sales data by Date?

 

Hopefully that makes sense.

Comments

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    You should be able to build an ETL that has the NOAA dataset as one input dataset and your sales data as another input dataset. You would then use the Group By tile on your sales data and group by city and use the Average function on your rain total. Next, use the Join tile to join that data with your NOAA data to bring that data together. There may need to be some additonal group by clauses depending on what else is in each dataset, but that is generally how you would do it.