Can you filter by a date range in the Magic ETL? Example My_Date = last 30 days?

Best Answer

  • MReznick
    MReznick Member
    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.

     

Answers

  • kshah008
    kshah008 Contributor

    Hi all,


    Can anybody help @MReznick with their question?

    Thanks!

  • quinnj
    quinnj Domo Employee

    @MReznick,

     

    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.

  • Shevy
    Shevy Contributor
    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.
  • kshah008
    kshah008 Contributor

    @MReznick, did any of the of the above replies help answer your question? 

  • MReznick
    MReznick Member
    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.

     

Categories