5 Replies Latest reply on Jun 30, 2017 10:29 AM by jdornbos

    Help splitting string to number fields


      I have data in the format of:


      that I would like to split into 4 individual values for further processing.


      Each month when I trigger this process, I'll have several hundred thousand records to process, so I'm looking for some efficiency over what I have so far. I can filter to get rid of the brackets. I recall that I used to be able to split strings based on a user-definable value, such as a comma - but I can't seem to find that function in FM 16 at the moment. Substituting a space for the comma, then using Left, Mid or Right words throws out the negative - which I need to keep. Finding the position of the comma, then grabbing the left, mid or right string just feels like I'm working way harder at this than I should - which is what brings me here for some help.


      While the values in my sample data are the same length, I don't think I can count on that being true for all values I'll have to process. These are USPS postal delivery "paths", that in turn make up a carrier route. I intend to store the mid-point of each path, which in this case will be average(-9267061, -9267052) and average(5197513, 5197269).


      TIA for any suggestions.


        • 1. Re: Help splitting string to number fields

          This calc will produce a return delimited value list.. just set the return type to be text

          Substitute ( field ; [ "[";"" ];["]";""];[",";¶])


          Then you can use GetValue() to do what you want with the values.

          See attached file

          1 of 1 people found this helpful
          • 2. Re: Help splitting string to number fields

            List( Substitute( "[[-9267061,5197513],[-9267052,5197269]]" ; [ "[" ; ¶ ] ; [ "]" ; ¶ ] ; [ "," ; ¶ ] ) )


            Will leave you with:






            You can then use GetValue( thatList ; whichNumber ) to get the number you need.


            Probably three or four other ways to do it too, including using the new JSON functions in FM16.

            2 of 2 people found this helpful
            • 3. Re: Help splitting string to number fields

              great minds think alike!

              • 4. Re: Help splitting string to number fields

                I don't know if your data source intends to be publishing JSON, but the format you show does fit the JSON array format, so you could use the new JSON functions to parse out the numbers:


                JSONGetElement ( "[[-9267061,5197513],[-9267052,5197269]]" ; "[1][0]" )

                // = -9267052


                You say you have several hundred thousand of these things to process. Is this path data something you receive as part of a larger text-based source? If so, you'll want to split the larger source into smaller chunks, and parse data out of the smaller chunks. The best way to do that depends on how the larger source data is formatted.

                2 of 2 people found this helpful
                • 5. Re: Help splitting string to number fields

                  Thanks for your help. The first option does the job for me.


                  @jbante - this is coming to me as json from the usps eddm api, 1 route at a time, with approx 1000 paths per route. I'm new to parsing json and I was too busy congratulating myself on parsing the route data and inserting it into a table, then parsing the path data for each route into another table, that I didn't recognize that I could get at the individual path elements as well. It makes my head hurt a bit to think about looping through the routes to loop through the paths to loop through the end points to grab the individual values. For today I'm going to substitute() and carry on, but thanks for pointing out the json solution that I didn't recognize.