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

Reply
Highlighted
Yellow Belt

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?


Accepted Solutions
Blue Belt

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

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'

All Replies
Blue Belt

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

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'
Yellow Belt

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

Thanks so much. This is perfect.

Blue Belt

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

@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'
Yellow Belt

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

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

 

Announcements
New to Dojo Please update your Dojo handle and personal settings. Click here!