Can you filter by a date range in the Magic ETL? Example My_Date = last 30 days?
Best Answer
-
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
query dataset.
SELECT GETDATE() AS CurrentDateTime, '1' AS 'CountofCurrentDateTime'
CurrentDateTime
CountofCurrentDateTime
1/26/2016 2:09 PM
1
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
Answers
-
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 -
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
query dataset.
SELECT GETDATE() AS CurrentDateTime, '1' AS 'CountofCurrentDateTime'
CurrentDateTime
CountofCurrentDateTime
1/26/2016 2:09 PM
1
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
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 396 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive