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
*COMMENT Visual Realisation
cfGetColumn ( text ; columnNumber ; columnDelimiter )
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
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
Let ( [
row = GetValue ( text ; 1 ) ;
cell = MiddleValues ( Substitute ( row ; columnDelimiter ; ¶ ) ; columnNumber ; 1 ) ;
cell = Substitute ( cell ; ¶ ; "" ) ;
countRows = ValueCount ( text )
countRows > 0 ;
cfGetColumn ( RightValues ( text ; countRows - 1 ) ; columnNumber ; columnDelimiter ; List ( result ; cell )) ;
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
How long the data string?
Add numbers between | and ¶
Substitute ( YourField ; [ "\"" ; "" ] ; [ "\\" ; "" ] ; [ "|" ; "\" or 1;\"" ] ; [ ¶ ; "\";0)+Case(\"" ] ) &
I don't prefer to use this
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.
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.
I am struggling to figure out your way, which I suspect is better than what I settled on
PatternCount ( $VehicleJSON ; "1st Row Seating Capacity " ) >0;
Middle ( $$VehicleJSON ; Position ( $VehicleJSON; "1st Row Seating Capacity "; 0;1)+ 27;
PatternCount ( $VehicleJSON ; "2nd Row Seating Capacity " ) >0;
Middle ( $VehicleJSON ; Position ( $VehicleJSON; "2nd Row Seating Capacity "; 0;1)+ 27;
PatternCount ( $VehicleJSON ; "3rd Row Seating Capacity " ) >0;
Middle ( $VehicleJSON ; Position ( $VehicleJSON; "3rd Row Seating Capacity "; 0;1)+ 27;
where the $VehicleJSON is the text
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 ]
Set Variable [ $curRow ; Value: GetValue ( $VehicleJSON ; $count )
If [ PatternCount ( $curRow ; "Row Seating Capacity" ) > 0 ]
Set Variable [ $tempText ; Value: List ( $tempText ; $curRow ) ]
Exit Loop If [ Let ( $count = $count + 1 ; $count > ValueCount ( $VehicleJSON )) ]
Set Variable [ $valueList ; Value: cfGetColumn ( $tempText ; 2 ; " | " ; "¶" ; "" ) ]
Set Variable [ $total ; Value: Sum ( Substitute ( $valueList ; "¶" ; "; " )) }
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.