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.
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
-
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'2
Answers
-
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'2 -
Thanks so much. This is perfect.
1 -
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'0 -
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
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 294 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 607 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 707 Beast Mode
- 49 App Studio
- 39 Variables
- 667 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 44 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 115 Manage
- 112 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive