8 Replies Latest reply: May 15, 2014 10:01 AM by fino _ RSS

    add ascending alpha suffix

    fino _

      good morning all,

      I have an issue which I am currently resolving in Excel after exporting summary from Monarch V9.01.

       

      Firstly my model has a calculated char field called ABCDE with formula:

       

      CONCATENATE_xl(trim(doc type),trim(val(company code)),trim(currency),LEFT(str(date) field,6),"A")

       

      so for example if

      doc type = XX

      company code = 12345

      currency = TRY

      date = 20080101

       

      then ABCDE becomes XX12345TRY200801A

       

      My upload process for this generated field dictates that I can only have 999 records for each combination of doc type, company code, currency and date. Therefore if there are more that 999 records I manually update the suffix "A" to a "B" for the next 999 records and so on ... I have had in the past to do this for up to 20K records and it is quite painful and tiresome.

      I have tried to incorporate rowno() in the output summary as it is not permitted.

      can this be acheived and if so my guess is a clever use of the if function

       

      regards

      fintan

        • add ascending alpha suffix
          Grant Perkins

          fintan,

           

          This response is completely untested but as your requirement is very specific and I can't think of anything I have that would similar and so quick to test I'll leave it to you for the time being!

           

          Rowno() relates to the data table and as such has a variable relationship with what is displayed.

           

          Recno() relates to the record's position in the original source data file and may be more useful. However it may not return blocks of 999 records if you filter/sort and so on.

           

          If ANY suffix would do provided the record could not be in a block exceeding 999 records then one might simply convert recno() to A, B, C based purely on a relationship to a number range. eg, 1-999 = A, 1000-1998 = B and so on. There are some functions that might make this concept more elegant than it looks here.

           

          There must be a better way but these are the observations that come to mind initially.

           

          An alternative would be to export the intermediate result with row numbers shown and then use that file as a new Monarch input allowing the row numbers to be fields in a new model and therefore usable as you need them to be.

           

          HTH.

           

          Grant

            • add ascending alpha suffix
              Data Kruncher

              Grant's on the right track. Using the Recno will be key to your solution.

               

              What you're after is A for records 1-999, B for 1000 to 1998, and so on.

               

              The forumla for calculated field Suffix will be:

              C[SIZE=2]hr(Int((Recno()-1)/999)+65)[/SIZE][/code]

               

              I think that'll relieve you of a particularly nasty task.

               

              Kruncher

               

              Edit: by row 25974 you'll be at your last Z, and you'll start getting other non-letter characters. If your typical data set is smaller than 25974 rows, you'll be OK. If not, either accept what it generates, or we need to make this smarter somehow.

                • add ascending alpha suffix
                  Nick Osdale-Popa

                  Grant's on the right track. Using the Recno will be key to your solution.

                   

                  What you're after is A for records 1-999, B for 1000 to 1998, and so on.

                   

                  The forumla for calculated field Suffix will be:

                  C[SIZE=2]hr(Int((Recno()-1)/999)+65)[/SIZE][/code]

                   

                  I think that'll relieve you of a particularly nasty task.

                   

                  Kruncher

                   

                  Edit: by row 25974 you'll be at your last Z, and you'll start getting other non-letter characters. If your typical data set is smaller than 25974 rows, you'll be OK. If not, either accept what it generates, or [SIZE="3"]we need to make this smarter somehow[/SIZE][/B].[/QUOTE]

                   

                  I love challenges like this!

                   

                  Smarter is what you'll get:

                  STRIP(STR(0,int((Recno()-1)/(999*26))+2,0,

                  chr((mod(Int((Recno()-1)/999),26)+65))),"0")[/code]

                  This will give you a single letter for records 1-25974

                  2 Letters for 25975-51948

                  And so on...

                   

                  I'll break this down:

                  This part breaks down each 999 records into groups of 26

                  mod(Int((Recno()-1)/999),26)+1[/code]

                   

                   

                  This will then translate the number into the corresponding letter.

                  chr((mod(Int((Recno()-1)/999),26)+65))[/code]

                   

                  Now comes the tricky parts:

                  This breaks up each alphabetical group into 25974 records, starting with 0 and ascending.

                  int((Recno()-1)/(999*26))+2[/code]

                  The +2 is used for the STR() function below.

                   

                   

                  This will take the group number and pass it as a length to the STR() function and pads the string with the given letter. For example, the first record would return A0.  Record 25975 returns AA0 and so on.

                  STR(0,int((Recno()-1)/(999*26))2,0,chr((mod(Int((Recno()-1)/999),26)65) ))[/code]

                   

                   

                  The full formula above removes the 0 from the string leaving just the letter(s).

                   

                  I've tested this with a table of 57679 records and that record returned a lettered suffix of "FFF".  Now just append the rest of your information.

                   

                  Not sure if that would be your lettering scheme but it's an idea that can work!

                   

                  Hope that helps!

                    • add ascending alpha suffix
                      Data Kruncher

                      I just knew you'd be all over this one Nick.

                       

                      I've been trying to model the Excel model (A-Z then BA-BZ and so on) to allow for many more rows. Not quite there yet.

                       

                      Care to give it a shot?

                        • add ascending alpha suffix
                          Nick Osdale-Popa

                          Are you trying to come up with a similar formula as I have in Excel?

                           

                          What have you got so far?

                            • add ascending alpha suffix
                              Grant Perkins

                              Hmm.

                               

                              I could try and get my head around this ..... or simply save a load of time and ask the people who will know!

                               

                              If the original records have been re-sorted, filtered or otherwise manipulated recno() may not give a result that groups in recno() order.

                               

                              If the requirement is simply for any old suffix to ensure upload that would not matter I guess. But if it needs to be controlled so that suffix A is exhausted at 999 occurrences before suffix B is used, how would you see the approach to that? (other than a 2 stage option?)

                               

                               

                              Grant

                              • add ascending alpha suffix
                                Data Kruncher

                                In Excel, this gets A in row 1, BN at 65535:

                                 

                                =IF((A1-1)/999>=26,CHAR((INT((A1-1)/999/26-1)65))&CHAR((MOD((A1-1)/999,26)65)),CHAR((INT((A1-1)/999)+65)))[/code]

                                 

                                Monarch's equivalent would be:

                                IF((recno-1)/999>=26,CHR((INT((recno-1)/999/26-1)65))CHR((MOD((recno-1)/999,26)65)),CHR((INT((recno-1)/999)65)))[/code]

                                 

                                If I'm right this should provide for an enormous amount of rows.

                                 

                                2nd edit: In fact, Excel tests show that the last ZZ row would be record 701,298. That's a fair data set.

                                  • add ascending alpha suffix
                                    fino _

                                    guys,

                                    I am really looking forward to getting my head around these solutions. I really appreciate your help and it is obvious that you are so willing to help. Attitudes like this always remind me of that film (42nd street) where the santa claus when asked for a toy that the store didnt have sent them to another store. This actually in turn lead to the original store getting more business! Your attitudes are amazing!

                                    cheers

                                    fintan