Parsing the first numeric value within a text string

[#1]

I'm looking for some load script code that will allow me to parse an integer value within a text string. All it has to do is start with the first occurrence of an integer (no negative values will appear) and keep reading until it finds the first non-numeric (also no decimals needed). Seems fairly straightforward, but I need it quickly, and, for some reason, I've never quite got the hang of loops in QlikView, despite the fact that I'm able to use them in virtually any other language.

[b_garside]

Could you provide a sample of this string?

[sunny_talwar]

If all that you need are numbers in your field, you might be able to use KeepChar() function like this:

Num#(KeepChar(FieldName, '0123456789'), '##') as FieldName

This will remove any non-numeric data and any negative signs from your field.

HTH

Best,

Sunny

[#4]

Brian,

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.

Thanks,

Scott

[swuehl]

If you have more complex text strings, you can make use of regular expressions, using a VBScript macro.

Learn here how to do this:

Regular expressions in the load script - The Qlik Fix! The Qlik Fix!

I used the sample file from above blog post to demonstrate the approach for your request, attached the QVW.

[tschullo]

  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.

[tschullo]

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:

  StringData:

LOAD * INLINE [
F1
"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"
];

StringParse:
Load F1,
mid( F1,findoneof( F1, '1234567890.'),
index(F1,purgechar( mid( F1,findoneof( F1, '1234567890.')),'1234567890.' ))
-findoneof( F1, '1234567890.')) as StrNum
Resident StringData;

[#8]

Thanks for the quick response! I'll resort to VBScript as a last resort.

[#9]

Thanks, Tony! I'll keep this in mind if the KeepChar is not sufficient for my needs.

[MarcoWedel]

Hi,

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

regards

Marco