Add date row in ETL (or other) for dates that do not exist.

I have a salesforce import and am pulling in records that are not created every day. But I'm trying to trend this information and its difficult to do since the last time a case of this type in salesforce was created was several weeks ago. So on my card, it looks as if domo hasn't updated since then, but really non cases in salesforce have been created.  I'd like to fill that gap with zeros but I'm having trouble figuring a way to do so. 

Screen Shot 2018-03-13 at 11.26.19 AM.png

So really I'd like to fill the gap here with zero values by week but also would like to see zero values until the next time we have a case created for this type.  

 

Any thoughts?

Best Answer

  • PodiumMason
    Accepted Answer

    Hey @jlazerus,

     

    One way that might be easy would be to power up the calendar.csv dataset (A dataset containing date information) from the Domo Dimensions connector.

     

    You can then left join from the calendar to your dataset on the date column, this will fill in the gaps in your data with dates and show the values where they exist. You could then do a beast mode on the value column to change any blank values to zeroes.

     

    You can append this to your domo instance url to get directly to the connector: 

     

    /appstore/connectors/com.domo.connector.domodimension 

     

    Hope this is helpful!

Answers