Can you filter by a date range in the Magic ETL? Example My_Date = last 30 days?
So, from the @Shevy solution I came up with this idea to solve my challenge.
Since Magic ETL doesn’t have the concept of NOW(), I can create it with a MSSQL
SELECT GETDATE() AS CurrentDateTime, '1' AS 'CountofCurrentDateTime'
1/26/2016 2:09 PM
This produces a one record table, I add a ‘Power of One’ field to the table, this will be for matching. If you are not familiar with the 'Power of One', ask Thomas Tobin from SFDC or Steve Molis in the SFDC community.
I now have a field that can approximate the Now() function in the Magic ELT by refreshing the dataset every 15 miutes.
Now I created a new ETL to test my idea,I add a Power of One Field (CountofSampledata) to the SampleData' dataset I want to age by days. I use a Join matching CountofCurrentDateTime & CountofSampledata to add the ‘CurrentDateTime’ stamp to the ‘SampleData’ table. Now, I added a ‘Date Operations’ action that calculates the ‘Days until date’ CurrentDateTime’ minus ‘SampleData.DateTime’. This produces a number value that can be filters.
By doing this with a seperate table it allows me to update the time stamp every time we run the ETL and I am able to use it across mutile dataset. It does not require the main data source to be refreshed to get the datetime stamp.
Thank you @Shevy for the pointing me in the right direction.0
A general design theme of Domo is to appropriately manipulate data at the latest stage possible. This generally requires doing less work upfront in the ETL process and allows greater flexibility at the actual card-building level.
Currently, there doesn't seem to be an easy way to filter data in Magic ETL, but it obviously is easy at the card level. You could also use a Beast Mode to create a filter based on the last 30 days that could be used in a card.
Hopefully that helps; but feel free to expound upon your use case here and we can brainstorm other solutions.0
This may not be a perfect solution but does work. In ETL you can add columns and then filter by those columns. So, as an example, I just took a data set - linked it to the "Date Operations" under "Edit Columns" and made a calculation that looked at two dates using the "Days Until Date" selection in the drop down. My data set has a "End of Business Date" column that tells me when the data is as of (previous business day) and I subtract the "Transaction Date" from that to generate a "days between Calc" in the data set. I then brought in the "Filter Rows" selection from "Edit Data" and filtered on my new column where it is less than or equal to 30. It worked in limiting my output for transactions that were within 30 days of the end of business date field. Let me know if that works for you!Dojo Community Member
** Please like responses by clicking on the thumbs up
** Please Accept / check the answer that solved your problem / answered your question.1
- 7.7K All Categories
- 5 Connect
- 921 Connectors
- 244 Workbench
- 479 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 446 Datasets
- 43 Visualize
- 199 Beast Mode
- 2K Charting
- 8 Variables
- 20 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 15 Manage
- 36 Governance & Security
- 29 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 14 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部