Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Parsing the first numeric value within a text string

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.

Tags (3)
9 Replies
b_garside
Valued Contributor

Re: Parsing the first numeric value within a text string

Could you provide a sample of this string?

Re: Parsing the first numeric value within a text string

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

Not applicable

Re: Parsing the first numeric value within a text string

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

MVP
MVP

Re: Parsing the first numeric value within a text string

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.

aschullo
Contributor II

Re: Parsing the first numeric value within a text string

  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.

aschullo
Contributor II

Re: Parsing the first numeric value within a text string

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;

Not applicable

Re: Parsing the first numeric value within a text string

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

Not applicable

Re: Parsing the first numeric value within a text string

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

Hi,

one similar solution tha tshould work regardless of the postition of the integer within the string:

QlikCommunity_Thread_167218_Pic1.JPG

QlikCommunity_Thread_167218_Pic2.JPG

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

Community Browser