6 Replies Latest reply: May 15, 2014 10:03 AM by Grant Perkins RSS

    Numeric Sorting

    Nancy10 _

      I'm working with a report that contains a column for Item Number. In the originating software, the item number is alphanumeric. We might have item numbers as follows:

       

      1

      2

      10

      20

      100

      200

      Apple

      Pear

       

      And the sort order above is the order in which the print in the software.

       

      In Monarch, this field has to be defined as a Character field. And Monarch does an ASCII sort:

       

      1

      10

      100

      2

      20

      200

      Apple

      Pear

       

      How can I get Monarch to do a numeric sort on the numerals and an alphabetic sort on the letters? The person who needs to rely on this report will be very unhappy with an ASCII sort.

       

      Nancy

        • Numeric Sorting
          Data Kruncher

          Hi Nancy,

           

          Because you seem to need the alpha items at the bottom of the list, I believe that you need a two part solution. If someone has a better solution, please share it with us.

           

          First, add a new numeric calculated field to your model named SortValue with an expression of: Val(Item)

           

          Now define a sort order with SortValue ascending. This willl put your alpha items at the top, hopefully in the order that they appear in the original report. Thus the need for a two part solution. Speaking of which...

           

          Now create two filters. One will filter SortValue>0 (name it Numerics), the other SortValue=0 (name it Alphas).

           

          At this stage you probably want to hide the SortValue field.

           

          Activate the Numerics filter. Create a new project export, and export your data. Save the model with Numerics in the file name and save a Numerics project file.

           

          Now activate the Alphas filter, and create a new project export, exporting the data and appending it to the original export. You may want to delete the first project export. Save the Alphas model, and an Alphas project file.

           

          That should do it, but if you want to repeat the exercise, you can now use the two project files you've created.

           

          Create a small batch file which first calls the Numerics project, and then the Alphas project, using the /pxall switch for both projects. That's easy, and why you only want a single defined project export in both project files.

           

          Again, if someone dreams up a better way...

           

          HTH,

          Kruncher

            • Numeric Sorting
              Data Kruncher

              About that better idea... :rolleyes:

               

              I recently posted something about a leading zeros solution. We can use that here too.

               

              Scratch the whole filter/project exports idea. We're down to one model now.

               

              You still need a SortValue calcuated field, but make it a Character field, and give it this:

               

              If(IsAlpha(Left(Item,1)), Item, Right("0000000000"+Item,10))[/CODE]

               

              You should have as many 0's as your Item field is wide, and the last number (10 in this case) needs to match the number of zeros.

               

              Now sort ascending on SortValue, and hide SortValue.

               

              Done.

                • Numeric Sorting
                  Nancy10 _

                  Kruncher,

                   

                  The first idea was pretty darn good; the second one is brilliant! That is an elegant solution! Thank you very much.

                   

                  I was surprised not to have found this question in the forum prior, and hope others will find this solution useful.

                   

                  I think the most interesting part of it is the Right section, where (if I understand it correctly) the 10 zeros are forced to the correct length by the 10 parameter at the end.

                   

                  Nancy