5 Replies Latest reply: May 15, 2014 10:07 AM by Olly Bond RSS

    Ranking

    Wendy _

      I am working on a large datafile which contains a list of numbers, I would like to get the highest frequency number and also the max amount. I want to ignore 0. for example,

      0,0,10,10,12,23,10,12,10,10.   To get the max, I use the max function in the calculated field. What do I do to get the most used number ie: 10?

      Can someone provide suggestion?

        • Ranking
          Olly Bond

          Hello Wendy,

           

          If you make a summary in Monarch, with the numbers as the key field, and the measure set to "count", you should get the most frequent. You can set the key field to sort by measure descending, so the most frequent comes out first, and also exclude 0 either via filter or by limiting the matching tab of the properties of the key field.

           

          HTH,

           

          Olly

            • Ranking
              Wendy _

              Olly, Thank for your response. 

              I tried it and this method may not work for what I need to do. Perhaps if I explain the purpose, it will help.

              I have about 300,000 of rows of record each with between 50-100 columns of cashflow schedules. These are cashflow schedules are normally the same each period but may have a slight variance, typically the last payment period.  Each row will have different schedule as well.  The objective is for me to find the last payment schedule and compare for exception.  The last payment column for each row is different.  I can do the max calculation for the higher payment but if I use min calculation, I get 0.

                • Ranking
                  Olly Bond

                  Hello Wendy,

                   

                  Any chance of emailing me a fragment of the report or posting a sample here? If we can't manage it with a calculated field and a summary then I'm confident a two pass approach should work.

                   

                  Best wishes,

                   

                  Olly

                    • Ranking
                      elginreigner _

                      Wendy, create a filter to remove the records with the 0 amount. When you setup your summary, have this as the active filter and your results should be what you want.

                        • Ranking
                          Olly Bond

                          Hello Wendy,

                           

                          I've emailed you two models to illustrate this approach, but for the thread here I thought I should explain why a summary won't work... With 80 columns of input data in a spreadsheet or database format, you'll go beyond the limit of the number of fields that Monarch can present in a summary. With that kind of record count, it's not a very efficient way to get your data even if you could summarise 300,000 records of 80 fields in Monarch.

                           

                          First step, is just to take in the table data and export it, without any changes, as a fixed width text file. Then take this in as a report in a new Monarch project,  trap the multiple columns as one big (or in your case, four big memo fields) and extract what you need using intrim to remove spaces, replace to remove " 0" and rsplit to extract the parts you need.

                           

                          Then you can process your data using a summary with five fields - a unique ID as key, two dates as items if you need them, and two numeric values as measures. You will need Monarch Pro to automate this, anything from v7 and up, but I presume you have that if you're working with this input format to start with.

                           

                          HTH,

                           

                          Olly