I am trying to build a saved search that utilizes a REGEXP (or something similar) to grab a specific set of values and compare them, only showing the largest number. Here is an example of a dataset that we are extracting the number from:
|4~8~9~105.000~35.000~~~~~~~||30~9~~108.000~270.000~~~~~~~||2~9~3~111.000~18.500~~~~~~~||4~9~6~114.000~38.000~~~~~~~||5~10~~120.000~50.000~~~~~~~||4~10~3~123.000~41.000~~~~~~~|
I've bolded the values I'm trying to extract and compare, some info regarding the format of these datasets, there can be more than 1 or just 1 (no set limit), there is always a '|' bracket at the beginning and end of each set and each value is separated by 11 '~' symbols. I am always trying to grab the second value in the set, here is a breakdown of how the individual sets are interpreted:
|[quantity]~[footage]~[inches]~[length in inches]~[total footage]~[piece mark]~[punch pattern]~[notch]~[punch]~[bundling]~[radius]~[pitch]|
For datasets where there is only a single set this isn't an issue, however when there are several like shown above, we only want to display the highest footage value.
Here was my thought on how to accomplish this:
MAX(REGEXP_SUBSTR({custom_dataset}, '\|(([^~]*)~){2}',1,1,'i',2))
Unfortunately this doesn't return any results. I am able to pull each sets footage if I change the following bolded value, but it won't dynamically scale based on the number of sets:
REGEXP_SUBSTR({custom_dataset}, '\|(([^~]*)~){2}',1,**1**,'i',2))
Any assistance would be greatly appreciated, perhaps regexp isn't the way to handle this, but it made the most sense to me.