4 Replies Latest reply: May 15, 2014 10:04 AM by Olly Bond RSS

    Limit Output Row Count

    Jim Weeks

      Hello, All.

       

      This might be an easy one.....I need to limit the number of rows exported to a delimited text file.  For example, the data may have 4,567 records but the output file can only have 2,000 (or X) records.  The table has been filtered as far as it can go and this is just a record count limit on the output.  In effect, I want to say "Of the 4,567 records filterd and sorted, give me the first 2,000". I see Rowno() but I am not able to add that to a filter.

       

      Any help would be much appreciated.

       

      THANK YOU!

       

      JimW

        • Limit Output Row Count
          Nick Osdale-Popa

          Hi Jim,

           

          I don't recall if this feature is in v9 or not:

           

          Create Filter

          Formula Based

          Give a a meaningful name (e.g. First2000)

          Do not put anything in the expression (Monarch will put 1 in there)

          Select the Advanced Tab

          At the bottom of that tab is a section called Row Count Limit.

          Select First and input 2000 in the box provided.

          Click OK.

          Click OK.

           

          Your data should now show the first 2000 records.

           

           

          If the above is not available, use the RecNo() for your filter:

          RecNo()<=2000

           

          The nice thing about this filter is that you can make the 2000 a runtime parameter and dynamically change the number of exported entries.

           

          Here's the help entry on RecNo():

          Returns the record number of a record in the table. You can think of the record number as the detail line number, since any given record number will always correspond to the same detail line from the report (e.g., Recno=1 always corresponds to the first detail line extracted from the report, and Recno=12 always corresponds to the 12th detail line, etc.). This applies even if the table has been sorted. This is not the case for the row numbers that you see at the left edge of the table.

           

          /quote

           

          In otherwords, regardless of the sort, this filter will always return the first n[/i] records as defined by the trap in the report.

           

          Hope that helps!

            • Limit Output Row Count
              Olly Bond

              Hi Jim,

               

              You mnetion that the table has already been filtered.

               

              Therefore, you'll need to either add this advanced condition (First x rows) to the existing filter, or create a compound filter to get the results you need. On its own, you'd get the firxt x rows from an otherwise unfiltered table...

               

              Best wishes,

               

              Olly

                • Limit Output Row Count
                  Jim Weeks

                  Thanks to both of you for the replies!

                   

                  I have used the Row Limit in the Advanced Filter Tab before and that would work very well here but I was trying for something dynamic where I could pass the number from another source.

                   

                  As I understand the Recno() function, I have already filtered and sorted the file so the Recno() results are pretty random.

                   

                  A little cumbersome but I was thinking of exporting the entire table (after filtering and sorting) with the Rowno(). Then re-import the file and the Rowno() would then simply be a sequential number that I could filter on.

                   

                  I'll keep messing with it to see what I can come up with.  If anyone can think of anything else, please let me know.

                   

                  Thanks again for your help!!

                   

                  Jim

                    • Limit Output Row Count
                      Olly Bond

                      Hello Jim,

                       

                      It all depends on what you mean by "pretty random".

                       

                      If there's a legal/audit requirement for the selection to be genuinely random and not to be possibly determined by report layout, filter and sort calculation, then Monarch does include the Rand() function.

                       

                      You can use this to assign a random number between 0 and 32767 to each record, and use functions / filters / row count limits based on this to export your subset.

                       

                      Best wishes,

                       

                      Olly