Hello all,
I need to replicate a split function from crystal in webi that picks up certain prices from a database value that splits the required numbers by a ; symbol and can have multiple prices in the same database column. Please see the image which shows the original crystal syntax and the two columns from the database. I’ve already done a ‘pos’ and ‘left’ combination but don’t this that’s going to work here. I can create objects in the report or the in the universe. I need a way of counting the number of ; across and pulling out the values in between.
use pos() to find the next separator
use length() to find the the number of charachters
use substr() to cut the value between
you have to use nested formulas to cut every single value
counting the separators can be done with total lenght of value minus lenght of value after replacing all the “;” with “”
if you are on 4.3 SP1 or later, you can set a “start” and “end”-position in POS and there is also a new RPOS formula