3 Replies Latest reply: May 17, 2016 2:34 PM by Stacy Litke RSS

    Every Nth Account?

    Stacy Litke

      I'm new to Monarch and I've tried hitting up the help material first and I'm not having any luck so I'm hoping someone here will know.  I've selected our account base and grouped by account type (checking, savings, etc.) and now I want to select every Nth account as a data sample, but that variable will be different for each group.  So for example, I may want every 50th account in the checking group, but every 20th account in the savings group.


      A) Does Monarch have an "every nth" function? Can't seem to find it, so if it does I'd love someone to point me in the right direction.

      B) Do you think I can change up the Nth variable within one table, or should I create separate tables for each group?


      Any ideas you have would be appreciated.   Thanks!.

        • Re: Every Nth Account?
          Olly Bond

          Hello Stacy,


          You might find this helpful - a webinar on some Monarch techniques called "Invisible Data" that covers the Recno() and Rowno() functions:https://datawatch.wistia.com/medias/fa64bjflsm


          The answer is yes - it always is with Monarch - but you might need a little preparation. If your data was in separate tables, it's easy, a filter defined as mod(recno();50)=1 will pick out the 1st, 51st, 101st etc record from the data.


          To combine it and get the results you need you'd need to make sure all the checking accounts were sorted before all the savings accounts, and then have a filter like (type="checking" .and. mod(recno();50)=1) .or. (type="saving" .and. mod(recno();20)=1)


          But - and do please play around with the functions and watch the webinar for tips - Recno() is not the same as Rowno() - record numbers are calculated from the data source, before sorting and filtering. If you need to filter and sort the data first to get the checking accounts above the saving accounts, you'll need to define a calculated field based on Rowno() and then export the table, then open it back up again.


          Best wishes,



          • Re: Every Nth Account?
            Stacy Litke

            Just to close the loop on this one in case anyone every references it, I took Olly's advice and did some playing. 


            I landed on using a formula based filter Recno()/50=Int(Recno()/50) for every 50th account.  Worked like a charm.  Little bit of overhead moving files back and forth between Monarch and Data Prep studio, but got it done quickly none the less.


            Thanks Olly!