8 Replies Latest reply on Mar 8, 2016 10:06 PM by user19752

    parsing text question

    Tom_Droz

      FM14ADV

       

      I have a field that has a lot of data, including something like this:

       

      Headlights Dusk Sensor | dusk sensing

       

      Seating Configuration

      1st Row Seating Capacity | 2

      2nd Row Seating Capacity | 3

      3rd Row Seating Capacity | 2

       

      Specifications

       

      I am trying to write a statement that would grab the 2,3,2 then add them to show 7

       

      Any ideas on how to do that?

       

      Thanks!!

       

      Tom

       

        • 1. Re: parsing text question
          Mike_Mitchell

          Hey, Tom. Couple of different approaches:

           

          1) You can write a script to loop over the rows one at a time. Then you get:

           

               GetValue ( Substitute ( currentRow ; " | " ; "¶" ) ; 2 )

           

          gives you the number in that row. Just keep a running count and when you run out of rows, you're done.

           

          2) You can use a custom function to extract the last column, then use the Sum function to total them up. Something like this:

           

               Sum ( Substitute ( cfGetColumn ( yourData ; 2 ; " | " ; "" ) ; "¶" ; "; " ))

           

          The cfGetColumn function is listed below. HTH

           

          Mike

           

          /*

          GetColumn function

           

          Author

          *COMMENT Visual Realisation

           

          Format

          cfGetColumn ( text ; columnNumber ; columnDelimiter )

           

          Parameters

          text - any text expression or text field

          columnNumber - any numeric expression or field containing a number

          columnDelimiter - any text expression or text field

          result - initially empty; used for tail recursion

           

          Data type returned

          text

           

          Description

          Returns a carriage return-delimited list of values from the requested column in a tabular text array.

           

          July 7, 2008

           

          Modified February, 2014 to use tail recursion

          Mike Mitchell, Net Caster Solutions

          www.netcastersolutions.com

           

          */

           

          Let ( [

          row  = GetValue ( text ; 1 ) ;

          cell = MiddleValues ( Substitute ( row ; columnDelimiter ; ¶ ) ; columnNumber ; 1 ) ;

          cell = Substitute ( cell ; ¶ ; "" ) ;

          countRows = ValueCount ( text )

          ] ;

           

          Case (

          countRows > 0 ;

          cfGetColumn ( RightValues ( text ; countRows - 1 ) ; columnNumber ; columnDelimiter ; List ( result ; cell )) ;

           

          result

           

          )

          )

          • 2. Re: parsing text question
            Tom_Droz

            Mike

            You are probably right but I am not getting how to find those particular columns.  Not clear on how I would use this

            I will look at it more and see if I can figure it out

             

            There are a 100+ other rows in the data and I have no idea where these item will show up or if they will show up

            • 3. Re: parsing text question
              user19752

              How long the data string?

               

              Add numbers between | and ¶

               

              Evaluate (

              "Case(\"" &

              Substitute ( YourField ; [ "\"" ; "" ] ; [ "\\" ; "" ] ; [ "|" ; "\" or 1;\"" ] ; [ ¶ ; "\";0)+Case(\"" ] ) &

              "\";0)"

              )

               

              I don't prefer to use this

              • 4. Re: parsing text question
                taylorsharpe

                Having data in a field like this is called non-normalized data. 

                 

                I would write a script that takes the data and converts it to a table with an Item field, Description Field, Amount and have a summary field totalling Amount.  Once normalized, such calculations are easy. 

                 

                But, yes, you can do some cool things like Mike showed above.  The problem I find is that often non-normalized data has poor quality such as not being consistent (extra tab here, extra carrier return there, a bunch of blank space, etc), making calculations like Mike showed not yield the desired results.  So normalizing it and cleaning it up helps out a lot.  But it also will make you have to work at cleaning up data that doesn't parse out into normalized data. 

                • 5. Re: parsing text question
                  Mike_Mitchell

                  I had assumed you'd already isolated the rows you needed. If that's not the case, then you'll probably need to loop over the rows and look for a pattern count. Example:

                   

                       Case ( PatternCount ( currentRow ; "Seating Capacity" ) > 0 )

                   

                  Move any rows that meet those criteria to a new variable, then parse it.

                   

                  But Taylor is right. Text parsing can be fragile if the incoming data are not consistent. These are "fun" problems for that reason.  

                  • 6. Re: parsing text question
                    Tom_Droz

                    I am struggling to figure out your way, which I suspect is better than what I settled on

                     

                    Case (
                    PatternCount ( $VehicleJSON ; "1st Row Seating Capacity " ) >0;

                    Middle ( $$VehicleJSON ; Position ( $VehicleJSON; "1st Row Seating Capacity "; 0;1)+ 27;

                    1 )
                    ;0)
                    +
                    Case (
                    PatternCount ( $VehicleJSON ; "2nd Row Seating Capacity " ) >0;

                    Middle ( $VehicleJSON ; Position ( $VehicleJSON; "2nd Row Seating Capacity "; 0;1)+ 27;

                    1 )
                    ;0)

                    +
                    Case (
                    PatternCount ( $VehicleJSON ; "3rd Row Seating Capacity " ) >0;

                    Middle ( $VehicleJSON ; Position ( $VehicleJSON; "3rd Row Seating Capacity "; 0;1)+ 27;

                    1 )
                    ;0)

                     

                    where the $VehicleJSON is the text

                    • 7. Re: parsing text question
                      Mike_Mitchell

                      You need to extract the rows you need in order for this to work. You can loop over the $VehicleJSON variable and store it in another variable before you process. Example:

                       

                      Set Variable [ $count ; Value: 1 ]

                      Loop

                           Set Variable [ $curRow ; Value: GetValue ( $VehicleJSON ; $count )

                           If [ PatternCount ( $curRow ; "Row Seating Capacity" ) > 0 ]

                                Set Variable [ $tempText ; Value: List ( $tempText ; $curRow ) ]

                           End If

                           Exit Loop If [ Let ( $count = $count + 1 ; $count > ValueCount ( $VehicleJSON )) ]

                      End Loop

                      Set Variable [ $valueList ; Value: cfGetColumn ( $tempText ; 2 ; " | " ; "¶" ; "" ) ]

                      Set Variable [ $total ; Value: Sum ( Substitute ( $valueList ; "¶" ; "; " )) }

                       

                      • 8. Re: parsing text question
                        user19752

                        This was not usable if there is | between | and ¶ , but op seems need to add more specific number, then changing "|" to "Row Seating Capacity" makes it stable.