Get First of Month in ETL
I'm creating an ETL flow that aggregates data by month. Is there a way to roll up my date field to just the first day of that month? I can extract the Month and the Year separately, but I need a result field that combines them back into a single 'First Day of the Month' field.
I.e. RegistrationDate = July 3 2018, First Day of the Month = July 1 2018
Right now the closest i can get is RegistrationMonth = 7, RegistrationYear = 2018. How do i put those back together to get July 1 2018 (or 7/1/2018 works too)? It needs to be done in the ETL process, not in a Beast Mode on the card because I want the aggregated value to be a distinct count and if I aggregate to the date level in ETL, it could result in duplicate counts when i aggregate again in the card to the month level.
Thanks
Comments
-
If you've got your Month and Year already, you could do the following:
1. Add a constant where the value is 1 and the type is text
2. Add a Combine Columns and follow the screenshot below to combine the Month, 1, and Year into a string your example + mine would result in a string of 7/1/2018
3. Use Set Column Type with 'Date' and 'Month First' to convert it to a date type.
The steps and flow order above should give you a new Date column where all dates are the first of the month.
Let me know if you have any questions,
Valiant_Ronin
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.1 -
I know you were looking for the first day ability, but that does not appear to be an option. If last day would be an option then I could help, but it will involve a MYSQL dataflow. Here is the syntax that you would use.
Select last_day(Date) as 'Month End Date'
This will create a new column that returns the last day of the month from the date column you are working in.
1 -
Two ways come to mind
1
Create a in excel a dataset that has two columns "Date" and the matching "First day of the month date".
Join that to your current dataset. Not elegant but it will work.
I have a fiscal calendar dataset that has this plus last day of month, quarter, year, half etc.
2
In magic ETL
Convert the date to decimal
Multiply "Day of Month" by 1000*60*60*24 (converts from day to miliseconds)
Subtract that from Date as decimal
Convert back to Date
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive