9 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    Multiple Arguments

    JCC _

      I have a report that I am trying to extract part of a field that is inconsistant in format. Sample of field below:

      REPORT_ID 

      VR2600G-RMD

      VR2600G#ALB

      VR2600GAPN

      VR2600GJCK1

      I am currently using the LSPLIT function but I am have difficulty in getting multiple arguments combined together to accomplish my goal. Btw the end result needs to be just the following:

      RMD

      ALB

      APN

      JCK1

      I currently have the following expression:

      LSPLIT(report_id,2,"#",2)+LSPLIT(report_id,2,"-",2). However, I am unable to build the rest of the expression to accomplish the remainder of the scenerios. Any assistance would be greatly appreciated.

        • Multiple Arguments
          Grant Perkins

          Hi,

           

          To a large extent the way you approach this may be dependant on which version of Monarch you have. V8 would offer some excitingly simple approaches - maybe.

           

          Need to clarify things first.

           

          In the sample all the data start VR2600G. Is that always the case (i.e. a fixed beginning to the line) for a single report? The fix might be fixed text string or a variable string but a fixed number of characters.

           

          Taking your use of SPLIT - you could split always on the "G" for example then process the right hand section to eliminate any non alpha or numeric characters. (If you have V7 or later STRIP could do that for example.)

           

          Or you couold use your existing LSPLIT as part of an IF() formula which will accept up to 36 nested if statements.

           

          So it would read something like;

           

          IF(LSPLIT(report_id,2,"#",2)=not blank,

          LSPLIT(report_id,2,"#",2),

          IF(LSPLIT(report_id,2,"-",2)=not blank,

          LSPLIT(report_id,2,"-",2),

          IF(....

           

          etc .....)

           

          For the data where you have either 3 or 4 characters required from the end of the data string (assuming you can define a rule or how many characters are required) you could look at

           

          SUBSTR() working from a know starting point, e.g. anything after the letter G position, so everything after position 7.

           

          Or you could use RIGHT for 4 characters BUT IF the data string LEN()is only 10 characters rather than 11, use RIGHT for 3 characters.

           

          Possibly INSTR() could be useful as well and if you have V& or higher, the STRIP function could help.

           

          If you can let us know which version of Monarch we have available to play with and whether the examples you have provided represent all of the possibilities or whether they are generic representations of the sort of problem you face, I am sure we can come up with some working pro-forma examples for you.

           

          As there are many possibilities I think it is worth looking for the best option available in your circumstances.

           

          Hope you agree.

           

          Grant

          • Multiple Arguments
            JCC _

            Grant,

            Thank you so much for the assistance. The report does always start with VR2600Gxxx OR VR2600G-xxx OR VR2600G#xxx or in one case only it is VR2600GJCK1. I will try to send a larger sample of the raw unedited report if I can, else I will send a larger sample.

            MM_YY  REPORT_ID   RPT_ID RPTID

            1/05  VR2600G#ALB #ALB   ALB  

            1/05  VR2600G#ALB #ALB   ALB  

            1/05  VR2600GAPN  APN         

            1/05  VR2600GAPN  APN         

            1/05  VR2600G#AUG #AUG   AUG  

            1/05  VR2600G-CHR -CHR   CHR  

            1/05  VR2600G-CHR -CHR   CHR  

            1/05  VR2600G-CH2 -CH2   CH2  

            1/05  VR2600G-CH2 -CH2   CH2  

            1/05  VR2600G#COL #COL   COL  

            1/05  VR2600G#COL #COL   COL  

            1/05  VR2600G-FTS -FTS   FTS  

            1/05  VR2600G-FTS -FTS   FTS  

            1/05  VR2600G-FTW -FTW   FTW  

            1/05  VR2600G-FTW -FTW   FTW  

            1/05  VR2600G-GNV -GNV   GNV  

            1/05  VR2600G-GNV -GNV   GNV  

            1/05  VR2600G-GN2 -GN2   GN2  

            1/05  VR2600G-GN2 -GN2   GN2  

            1/05  VR2600G-GN3 -GN3   GN3  

            1/05  VR2600G-GN3 -GN3   GN3  

            1/05  VR2600G-GN1 -GN1   GN1  

            1/05  VR2600G-GN1 -GN1   GN1  

            1/05  VR2600GGRN  GRN         

            1/05  VR2600GGRN  GRN         

            1/05  VR2600GJCK1 JCK1        

            1/05  VR2600GJCK1 JCK1        

            1/05  VR2600GJCK1 JCK1        

             

            As you can see I have used a SUBSTR function but it produces some unwanted characters for field rpt_id:SUBSTR(report_id,8,4).

            In rptid I get some but not all of the 3 letter ids that I need with:

            rptid:LSPLIT(report_id,2,"#",2)+LSPLIT(report_id,2,"-",2),. I am fortunate to have Monarch to work with, but unfortunate in the fact that I have to use an older version. Are we ready to go back to the stone ages.....4.05...ouch.

              :eek:

            • Multiple Arguments
              Bruce _

              JCC

               

              I would split the string into 2 parts "VR2600G" and the rest. The 2nd part strip out the "#" or "-" as needed with an if and substr or replace.

               

              Something like:

              if (left(2ndPart),1).notin. ("-","#"),2ndpart, substr(2ndPart,2)).

               

              Let us know if this helps.

              • Multiple Arguments
                JCC _

                Bruce,

                Would that be the complete expression or do I use it in conjunction with something else? I get an "invalid item" error with just that expression at the first occurence of "2ndpart" and the cursor is between the "2" and the "n".   :confused:

                • Multiple Arguments
                  Grant Perkins

                  I'm with Bruce on this and I will try to work out the optimal approach later today (busy for a few hours just now).

                   

                  The only problem I have is remembering what v4.05 does not have - I still have a copy but not an installed one and not available to me at the moment!

                   

                  You won't access to the .IN. or .NOTIN. operators for example, or the REPLACE or STRIP functions. So the formula needs to be derived in a longer way.

                   

                  It would be nice to make it generic if possible for ease of maintenance.

                   

                  More later.

                   

                  Grant

                  • Multiple Arguments
                    JCC _

                    Ok...since for some reason I don't understand the "wildcard concept", read couldn't get to to work, I just did it the long way. I really think the concept should work, it just didn't for me. If you can direct me to a shorter expression and how it should work, I would like to see it. So this is what I came up with:

                    IF(INSTR("-",LSPLIT(report_id,2,"G",2)),RIGHT(LSPLIT(report_id,2,"G",2),3),IF(INSTR("#",LSPLIT(report_id,2,"G",2)),RIGHT(LSPLIT(report_id,2,"G",2),3),IF(INSTR("G",LSPLIT(report_id,2,"G",2)),RIGHT(LSPLIT(report_id,2,"G",2),3),IF(INSTR("A",LSPLIT(repor t_id,2,"G",2)),RIGHT(LSPLIT(report_id,2,"G",2),3),IF(INSTR("J",LSPLIT(report_id,2,"G",2)),RIGHT(LSPLIT(report_id,2,"G",2),4),IF(INSTR("M",LSPLIT(report_id,2,"G",2)),RIGHT(LSPLIT(report_id,2,"G",2),3),IF(INSTR("P",LSPLIT(report_id,2,"G",2)),RIGHT(LSPL IT(report_id,2,"G",2),3),IF(INSTR("S",LSPLIT(report_id,2,"G",2)),RIGHT(LSPLIT(report_id,2,"G",2),3),IF(INSTR("K",LSPLIT(report_id,2,"G",2)),RIGHT(LSPLIT(report_id,2,"G",2),3),"")))))))))

                      :eek:

                    • Multiple Arguments
                      Grant Perkins

                      Originally posted by JCC:

                      Bruce,

                      Would that be the complete expression or do I use it in conjunction with something else? I get an "invalid item" error with just that expression at the first occurence of "2ndpart" and the cursor is between the "2" and the "n".    :confused:  /b[/quote]Try this.

                       

                       

                      I think these operators and functions are available in V4.05 - not yet been able to check though.

                       

                      1. Split the Report_ID field a follows;

                      [font="courier"]LSplit(Report_id,2,"G",2)[/font][/quote]or

                      [font="courier"]SUBSTR(Report_id,8,4)[/font][/quote]Your calculated field needs to be "Character". This new field (if you choose to create it, is refered to as First-Split in the formula below).

                       

                       

                      2. Clean up the first character (Anything with ascii value less than A (ASCii 65) in this example)

                      [font="courier"]if((Asc(First_Split)<65),SUBSTR(First_Split,2,4),First_Split)[/font][/quote]Read as

                       

                      If the ascii value of the first character in the field First_Split is less than 65 (A) then SUBSTR the field from the second character for 4 characters. (Note that the number 4 is arbitrary -cold have used 20 for example.)

                       

                      3. To do this in one step substitute the First_Split field with the formula that creates it. E.G.:

                      [font="courier"]if((Asc(SUBSTR(Report_id,8,4))<65),SUBSTR(SUBSTR(Report_id,8,4),2,4),SUBSTR(Report_id,8,4))[/font][/quote]4. That should be it. Add a little seasoning to taste if the base data content in other examples follows different rules.

                       

                      5. If this does not work in 4.05 I will need to do some digging to find the combination that will.

                       

                      Enjoy.

                       

                      Grant

                      • Multiple Arguments
                        JCC _

                        Grant,

                        That is just incredible.    Works like a champ. Now I just need to work on my IT Procurement group and get them to update us to the 21st century with a current version of Monarch.

                        Thanks again for all you great support.

                          smile.gif[/img]

                        • Multiple Arguments
                          Grant Perkins

                          Glad you like it!

                           

                          There is some really clever stuff possible with Monarch - I have seen several examples that are way beyond my level of ability to think things through.

                           

                          I fully recommend version 8, especially if you maybe want to do something with pdf files from time to time.

                           

                          But even if you don't there are some excellent new features which add functionality that was not possible before or simply make some of the more complex things much easier to do than previously. So terrific time saving available to get to  generating a result.

                           

                          That sort of thing is especially obvious if moving from one of the much earlier versions.

                           

                          That said I still have a copy of verison 3 somewhere and I know of others who still RUN with version 1 for DOS! Longevity is another attribute ...      

                           

                           

                          Grant

                           

                          Edited to add:

                           

                          I liked your nested IF() formula BTW. Good practice. Sometimes that IS the only way to go!