4 Replies Latest reply on Aug 6, 2018 12:26 PM by Joshua Milligan

    Input Filter to Most Recent Date (Prep)

    Curtis Stouwie

      Hey T-Community,

       

      Running out of ideas on an input filter calculation.


      I am attempting to impose a filter on the input stage of the flow to filter a date column to the most recent. I have a wildcard input with all files having a column with the date of the data pull. For performance reasons, I'd prefer to do it on the input stage than doing after the input, i.e. add clean step, add calculated field then filter on new calculated field.

       

      I don't think a example file is needed however can provide if needed.

       

      Thanks!

        • 1. Re: Input Filter to Most Recent Date (Prep)
          Curtis Stouwie

          This might help... The closest I got was this formula:

           

          DATEDIFF('day',[Date],TODAY())>7

           

          That should filter anything that was pulled with-in the last week but it returns 0 results.

          • 2. Re: Input Filter to Most Recent Date (Prep)
            Joshua Milligan

            Curtis,

             

            If your input were a server based data source that supports Custom SQL, you would be able to write the filter in the SQL Code.  It would be something like:

             

            SELECT *

            FROM [Table]

            WHERE [Date] = (SELECT MAX([Date]) FROM TABLE)

             

            Since it is file based, you will have to do the filtering using some logic in the flow itself.  One possible approach is detailed here: Latest Snapshot in Tableau and Tableau Prep (Maestro) | VizPainter

             

            Hope that helps!

            Joshua

            2 of 2 people found this helpful
            • 3. Re: Input Filter to Most Recent Date (Prep)
              Curtis Stouwie

              Yes it does, it confirms what I suspected. That it's not possible to do this in the Input Filter just yet.

               

              I ran across that Latest Snapshot post in my searching and as I take it it would mean adding steps to the flow where I was hoping to do this on the first step since I figured that would increase performance since it wouldn't have to cache everything.

               

              Maybe i'll add to the ideas.


              Thanks Josh!

              • 4. Re: Input Filter to Most Recent Date (Prep)
                Joshua Milligan

                Curtis,

                 

                The interesting thing about performance in Tableau Prep is that there are two run modes: Design and Batch.

                 

                Here is my best guess: Design (where you are clicking steps and seeing the data profile change) would likely have a decreased performance (or at least increased memory usage) because it would cache the various steps along the way.  However, Batch (where you run the flow to get an output) performance likely would not be impacted because it collapses the flow into an optimized query against the source - so the multiple steps turn into a single query that would somewhat resemble the query I wrote above.

                 

                I would definitely up-vote an idea for more robust filtering of the Input step as it would make the design experience more pleasant in some cases and would give me more control over potentially avoiding sampling.

                 

                Best Regards,

                Joshua

                1 of 1 people found this helpful