Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I'm trying to extract the number of a materiel within a string.
The problem is that users tend to add additional information into the input field (web page), so that when I load the the data with qlik, the field has more information then I want.
Lets say I have a string like this:
let vExampleString1 = 'This is my number #1234567890';
let vExampleString2 = 'Package number #42Part 1';
I would like to extract the data to be:
'1234567890'
'42' // important only the 42 and not the 1 from 'Part 1'
A few rules:
1) the number in Question always starts with a #
2) the number in question ends with the fist non number
I have tried to accomplish it with:
Mid('$(vExampleString2)' , Index('$(vExampleString2)', '#'), 10)
KeepChar('$(vExampleString2)','0123456789')
but wasn't able to get it done. Can someone help me?
May be this
Table:
LOAD *,
Left(Mid(String&'a', Index(String&'a', '#')+1), FindOneOf(Lower(Mid(String&'a', Index(String&'a', '#')+1)), 'abcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+{}:"<>?/.,'';][=-`')-1) as Number;
LOAD * INLINE [
String
This is my number #1234567890
Package number #42Part 1
];
May be this
Table:
LOAD *,
Left(Mid(String&'a', Index(String&'a', '#')+1), FindOneOf(Lower(Mid(String&'a', Index(String&'a', '#')+1)), 'abcdefghijklmnopqrstuvwxyz~!@#$%^&*()_+{}:"<>?/.,'';][=-`')-1) as Number;
LOAD * INLINE [
String
This is my number #1234567890
Package number #42Part 1
];
It's so nice solution by adding 'a' at the end of String to make FindOneOf function work in any case
Hahahaha yes