7 Replies Latest reply on Nov 3, 2016 4:06 PM by lindseym

    Calculation Help -- nested functions

    lindseym

      I posted this earlier today, but after spending the better half of my day on it (I've learned a lot! but nothing pertaining to this portion of the original question)

       

      The calculation I am looking for help with is:

       

       

      If (Field Name) contains the word "Level" ; get everything (excluding the first space) after the word "Level" until you reach a "-" , if (Field Name) does not contain the word "Level" return TBD (highlighted)

       

       

       

      The (Field Name) contains different values for example, but all instances are separated by a dash [the last item in the list (or the first if there is only 1  item in the list does not have a dash after], the order of the values could be in any random order:

       

      Bldg North-Level 2-Section Beyond the Trees

      Level 1-Bldg A4

      Section Beyond The Trees

       

       

      Outcome:

       

       

      Field Name Contents: Bldg North-Level 2-Section Beyond the Trees

       

      If (Field Name) contains the word "Level" ; get everything (excluding the first space) after the word "Level" until you reach a "-" , if (Field Name) does not contain the word "Level" return TBD (highlighted)        would return 2

       

      If (Field Name) contains the word "Section" ; get everything (excluding the first space) after the word "Section" until you reach a "-" , if (Field Name) does not contain the word "Section" return TBD (highlighted)        would return Beyond The Trees

       

      If (Field Name) contains the word "Bubble Gum" ; get everything (excluding the first space) after the word "Bubble Gum" until you reach a "-" , if (Field Name) does not contain the word "Bubble Gum" return TBD (highlighted)        would return Screen Shot 2016-11-03 at 9.16.04 AM.png

       

       

       

      Thank you kindly,

       

      -L

        • 1. Re: Calculation Help -- nested functions
          erolst

          lindseym wrote:

          "Section" would return [Beyond the Trees] from (Bldg North-Level 2-Section Beyond the Trees)

          I think you've forgotten something in the explanation of the intended algorithm ... like

           

          'If (Field Name) contains the word "Section"'

          • 2. Re: Calculation Help -- nested functions
            lindseym

            I'll update right away. Thank you

            • 3. Re: Calculation Help -- nested functions
              karimhanafi

              Hi Lindseym,

               

              To my understanding, you need a function that takes 3 parameters:

              - A string, which is the original text ( Bldg North-Level 2-Section Beyond the Trees for example)

              - A substring, the word or expression you are going to select remaining text after (Level or Section)

              - A fail text, which will be shown highlighted in case the string does not contain the substring

               

              Here is a function that might accomplish what you want. It can be written in a smaller form without using the Let statement, but I find it simpler to understand this way. Also, this function was written in the data viewer with no DB open, so I have 2 nested let statements. The first Let Statement is not necessary to the function.

               

              // the first let statement is here only to input the variables as I prepared this demo function in the data viewer

               

              Let (

                  [

                    ~string = "Bldg North-Level 2-Section Beyond the Trees" ;

                    ~substring = "Level" ;

                    ~fail = "TBD"

                    ] ;

               

               

                    // -- core function, this is the actual function

               

                  Case (

               

                        // In order to start with simpler test, we first test the case where the substring is not contained in the script -> throw a failed test string (TBD or other) if true

               

                    patterncount ( ~string ; ~substring ) = 0 ; // This means the string does not contain the substring

                              textstyleadd ( ~fail ; HighlightYellow ) ;

               

               

                    // Else, we proceed to extrating alla the characters after our Substring

                    // using FM native function, we determine the substring position in the string, the length of the string and then extract all characters after Position + Length

                    Let (

                              [

                              ~lenString = Length ( ~string ) ;

                              ~lenSubstring = Length ( ~substring ) ;

                              ~posSubstring = Position ( ~string ; ~substring ; 1 ; 1 ) ;

                              ~return = Middle ( ~string ; ~posSubstring + ~lenSubstring ; ~lenString )

                              ] ;

               

                              // At the end, we trim the result, so we get rid of any unwanted spaces

                              Trim ( ~return )

                      )

               

               

                    )

               

               

              // -- end of core function

               

               

              ) // closing first let statement

              • 4. Re: Calculation Help -- nested functions
                lindseym

                That was really helpful to see how things work. Thanks!

                 

                 

                I got it to kind of work.... Looks like it's having an issue with the length? 'll keep playing with and trying to figure it out oh, the TBD works great!

                Screen Shot 2016-11-03 at 3.29.21 PM.png

                 

                I think one problem is that the column could read:

                 

                Building North-Section Around the Tree-Level 3

                 

                or

                 

                Section Around the Tree--Building North-Level 3

                 

                or

                 

                Level 3-Section  Around the Tree-Building North

                 

                 

                 

                 

                I could change the import data to put a character around Ex: Level *3*-Section  *Around the Tree*-Building *North*

                • 5. Re: Calculation Help -- nested functions
                  lindseym

                  Oh, I think it is getting everything after the "~substring word"..... so could I do one more nested fiction that grabs everything from the "dash" on and deletes it?

                  • 6. Re: Calculation Help -- nested functions
                    lindseym

                    I think this would work...

                     

                    GetValue ( Substitute ( textField ; "-" ; ¶ ) ; 1 ) (I've tested this alone and it gets the desired results, but not sure how to incorporate it into the below calculation.

                     

                     

                    philmfdjunk ( I'm tagging you since I you commented on this post: get all text before character  , and are the first name i recognize that is still active.   )

                     

                    Let (

                        [

                          ~string = "Space" ;

                          ~substring = "Level" ;

                          ~fail = "TBD"

                          ] ;

                     

                     

                          // -- core function, this is the actual function

                     

                        Case (

                     

                              // In order to start with simpler test, we first test the case where the substring is not contained in the script -> throw a failed test string (TBD or other) if true

                     

                          patterncount ( ~string ; ~substring ) = 0 ; // This means the string does not contain the substring

                                    textstyleadd ( ~fail ; HighlightYellow ) ;

                     

                     

                          // Else, we proceed to extrating alla the characters after our Substring

                          // using FM native function, we determine the substring position in the string, the length of the string and then extract all characters after Position + Length

                          Let (

                                    [

                                    ~lenString = Length ( ~string ) ;

                                    ~lenSubstring = Length ( ~substring ) ;

                                    ~posSubstring = Position ( ~string ; ~substring ; 1 ; 1 ) ;

                                    ~return = Middle ( ~string ; ~posSubstring + ~lenSubstring ; ~lenString )

                                    ] ;

                     

                                    // At the end, we trim the result, so we get rid of any unwanted spaces

                                    Trim ( ~return )

                            )

                     

                     

                          )

                     

                     

                    // -- end of core function

                     

                     

                    ) // closing first let statement

                    • 7. Re: Calculation Help -- nested functions
                      lindseym

                      Okay! I got it!!!!

                       

                       

                       

                      I replaced the very last function of Trim (~return)  with      GetValue ( Substitute ( ~return ; "-" ; ¶ ) ; 1 )

                       

                      Presto!!!

                       

                      Screen Shot 2016-11-03 at 4.06.21 PM.png

                       

                      EXACTLY what I was looking for! Thanks for your help!