5 Replies Latest reply on Feb 2, 2017 1:23 PM by Joshua Milligan

    How do filter the last 10,000 records

    Dan Burrell

      We have a set of data where we want to select the last 10,000 records

       

      I want to get the Last X records where they are in order ( being the 10,000 latest Records )

       

      However when I apply the following filter, I get the ID's from lower than I would expect.

      (There are criteria that means it's not simply the latest ID - 10,000)

       

       

      its almost like the records need ordering before the filtering takes place?

       

      I can drop an example here if it helps

       

      thanks!

        • 1. Re: How do filter the last 10,000 records
          Rohan Malusare

          Hi Dan,

           

          Can you share any sample workbook?

           

          Regards,

          Rohan Malusare.

          • 2. Re: How do filter the last 10,000 records
            Tharashasank Davuluru

            Hi,

            Can you post the sample?

            Do you have a date filed in the data  we can also control based on the date.

             

            Thanks,

            Tharashasank

            • 3. Re: How do filter the last 10,000 records
              Joshua Milligan

              Dan,

               

              Do you want the most recent (indicated by [As At]) 10000 Customer records (identified by [Customer Number])?  If so, then from your screenshot, it looks like you are very close.  However, you need to change Bottom to Top and  Count to Max.  That way, you are getting the 10000 customer records that have the most recent As At date.

               

              It might seem odd at first to use Top when you want rows that have been inserted most recently (typically we think of those as at the bottom), but in reality nearly every data source doesn't really have a reliable order (who knows where or how they are stored on disk -- and standards dictate that you can't count on consistent ordering unless you explicitly define it in a query).  You can define ordering using fields (which is what you are telling Tableau to do with Top 10000 [Customer Number] by Max [As At])

               

              Alternately, if [Customer Number] is a unique identifier in your data AND it increments in a predictable way, you could filter to the Top 10000 by Max [Customer Number].  But I suspect that this is not the case with your data because an [As At] field usually indicates that you can have multiple occurrences of (in this case) a single customer.  in which case, try the former approach.

               

              Hope that helps!

              Joshua

              3 of 3 people found this helpful
              • 4. Re: How do filter the last 10,000 records
                Dan Burrell

                You sir, are a star

                 

                This is exactly what I was doing incorrectly.

                 

                What i did to overcome my multiple record was to apply datasource filtering and context filters ( as each day on has one Customer record row, but as a whole the table has many )

                 

                Top 10,000 by Max Customer Number is ideal and returns what I want.

                 

                I think for some reason I was assuming the table was structured in a fashion that is top to bottom in order from highest to lowest.

                • 5. Re: How do filter the last 10,000 records
                  Joshua Milligan

                  Thanks Dan!  Glad it helped!