2 Replies Latest reply: May 15, 2014 10:08 AM by Olly Bond RSS

    Monarch question

    chickenjoy _

      Hi Monarch Experts,

       

      I am trying to create a calculated field where I can identify a change in a key value by a sequence number. For example, I have property A, B,and C ....see link below

       

      http://i225.photobucket.com/albums/dd38/peytimuti/table.jpg[/url]

       

       

      The sequence number would just be the next consecutive change in key value.

       

      Thank you very much for your help in advance.

       

      Chickenjoy.

        • Monarch question
          Data Kruncher

          The only certain solution that I can see for this at the moment requires (at least) two Monarch processes.

           

          In the first, create a summary using only the Property as a key field. Export the summary, and then use that output as the input for the second Monarch pass.

           

          In that model, build a new Numeric calculated field using the Rowno() function, and you'll have the numbering that you want.

           

          But then I imagine that you may need to export those values and use that output as an external lookup source for some other purpose.

           

          HTH,

          Kruncher

            • Monarch question
              Olly Bond

              Hello Chickenjoy,

               

              If you get a chance, (plug alert) watch the Invisible Data webinar. Using Rowno() and Recno(), possibly with two passes, will solve this problem for you and your users.

               

              Firstly, define a sort order on the table for the key values in the right order, then a null filter with advanced properties for unique key values. Then define a calculated field = Rowno(). This will give you the 1, 2, 3, you want.

               

              Export the table filtered and sorted as such, and then use this export as a lookup joined on the key value. This will bring in 1 against every instance of A, 2 against every instance of C, 3 against F and so on, changing on a month by month basis.

               

              This can all be automated neatly in two projects and a batch file.

               

              Best wishes,

               

              Olly