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

jlazerus
jlazerus Member
in Magic ETL

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
    PodiumMason Contributor
    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!

    **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

  • PodiumMason
    PodiumMason Contributor
    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!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • jlazerus
    jlazerus Member

    Thanks so much. This is perfect.

  • PodiumMason
    PodiumMason Contributor

    @jlazerus Great! Happy to help.

     

     

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • jlazerus
    jlazerus Member

    So this worked perfectly for me but now I have a follow-up question. I'm trying to do something similar except there's a series this time instead of just one value.  How can I do something similar with a series?

     

    See attached example

     

Categories