topic Re: Parsing the first numeric value within a text string in QlikView

Parsing the first numeric value within a text string

Fri, 05 Jun 2015 15:38:58 GMT

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.

Re: Parsing the first numeric value within a text string

b_garside — Fri, 05 Jun 2015 16:36:46 GMT

Could you provide a sample of this string?

Re: Parsing the first numeric value within a text string

sunny_talwar — Fri, 05 Jun 2015 16:43:52 GMT

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

Re: Parsing the first numeric value within a text string

Fri, 05 Jun 2015 16:46:10 GMT

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

Re: Parsing the first numeric value within a text string

swuehl — Fri, 05 Jun 2015 17:17:39 GMT

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.

Re: Parsing the first numeric value within a text string

tschullo — Fri, 05 Jun 2015 17:38:08 GMT

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.

Re: Parsing the first numeric value within a text string

tschullo — Fri, 05 Jun 2015 18:16:03 GMT

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;

Re: Parsing the first numeric value within a text string

Fri, 05 Jun 2015 20:40:14 GMT

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

Re: Parsing the first numeric value within a text string

Fri, 05 Jun 2015 20:41:09 GMT

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

Re: Parsing the first numeric value within a text string

MarcoWedel — Fri, 05 Jun 2015 22:20:26 GMT

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