Users have been directed to indicate the number of study sites for each country in a comment field. Initially, they should just enter the number of sites, but then update this with " Selected". There are some outliers, like "48 sites, 12 subjects", but this would require more logic than I want at the moment.
It seems I may be able to just get away with a KeepChar([field-name],'0123456789') for my purposes, then filter out any outlandish values, like the 4812 that would be returned in my example above.
However, if you have an idea for how I can just parse the first numeric value (the 48 from above, not the 12), I'd love to hear it.
If you have more complex text strings, you can make use of regular expressions, using a VBScript macro.
Learn here how to do this:
I used the sample file from above blog post to demonstrate the approach for your request, attached the QVW.
RegEx_comm167218.qvw 370.8 K
Given a string myString='my example11.2 text string'
the following expression works, whhich you could tweak into load code:
=mid( $(myString),findoneof( $(myString), '1234567890.'),
index($(myString),purgechar( mid( $(myString),findoneof( $(myString), '1234567890.')),'1234567890.' ))
-findoneof( $(myString), '1234567890.'))
If their are multiple numbers inyour strings ( abc123def456.7xyz ) you would have to expand om this, but hopefully this get you started.
I always try to avoid looping in script wherever possible.
PS: I just tried it in script and it work so long as their is only one number in the string and/or if their is a second number it is at the end of the string. Here is the script I used:
LOAD * INLINE [
"my example 11.2 text string99"
"my example 1108.2 text99 string" //note: will not work for this string without further tweaking
"my example 44.24 text string"
mid( F1,findoneof( F1, '1234567890.'),
index(F1,purgechar( mid( F1,findoneof( F1, '1234567890.')),'1234567890.' ))
-findoneof( F1, '1234567890.')) as StrNum
one similar solution tha tshould work regardless of the postition of the integer within the string:
LOAD *, Mid([field-name],FindOneOf([field-name],'0123456789'),FindOneOf(Mid([field-name],FindOneOf([field-name],'0123456789'))&'.',PurgeChar([field-name]&'.','0123456789'))-1) as FirstInteger INLINE [ field-name "48 sites, 12 subjects" "abc 48 sites, 12 subjects" "a48b sites, 12 subjects" "abc sites, 12 subjects" "abc sites, 12" "abc sites, 12 subjects 20" "4 8 sites, 12 subjects" "abc 4 8 sites, 12 subjects" ];
hope this helps
QlikCommunity_Thread_167218.qvw 150.2 K