topic Re: How to keep only numbers from string. in QlikView

How to keep only numbers from string.

HenryFoth_2021 — Sat, 16 Nov 2024 17:57:38 GMT

Hi Community.

I'm having some trouble trying to keep only 1 set of numbers from a string. The set of numbers are separated by spaces. The mount of spaces and numbers are not always the same. The only thing that is the same is the mount of set of numbers, 9.

Here an example of data set:

The set I wish to keep is the one painted in yellows.

Any suggestions?

Thanks in advance.

Re: How to keep only numbers from string.

dplr-rn — Fri, 16 Oct 2020 18:45:07 GMT

please elaborate. not clear what you mean by 'mount of numbers'

is the string 1 line in your picture ? and you want to get the yellow from that string?

did you try index or textbetween functions?

e.g. =TextBetween('2169 7775 0 0 9944 62 419 0 329',' ', ' ',4) returns 9944

Re: How to keep only numbers from string.

benvatvandata — Fri, 16 Oct 2020 19:27:01 GMT

My solution to your problem doesn't seem like the ideal way to do this... but in your script, you can pull your string field in like below to get your highlighted values:

(MyString represents whatever your fieldname is)

Table:

LOAD

subfield(ltrim(mid(ltrim(mid(ltrim(mid(trim(mid(MyString,index(MyString,' '))), index(trim(mid(MyString,index(MyString,' '))),' ',1))), index(ltrim(mid(trim(mid(MyString,index(MyString,' '))), index(trim(mid(MyString,index(MyString,' '))),' ',1))),' '))),
index(ltrim(mid(ltrim(mid(trim(mid(MyString,index(MyString,' '))), index(trim(mid(MyString,index(MyString,' '))),' ',1))), index(ltrim(mid(trim(mid(MyString,index(MyString,' '))), index(trim(mid(MyString,index(MyString,' '))),' ',1))),' '))),' '))),' ',1) as MyString

FROM [your file path];

Re: How to keep only numbers from string.

jwjackso — Fri, 16 Oct 2020 19:51:03 GMT

Similar to the other replies. Replace a combination of space and number with '|' that will be the delimiter. Then replace spaces with nothing. The use the SubField function to get the 5th field.

SubField(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(STR,' 0','|0'),' 1','|1'),' 2','|2'),' 3','|3'),' 4','|4'),' 5','|5'),' 6','|6'),' 7','|7'),' 8','|8'),' 9','|9'),' ',''),'|',5)

Re: How to keep only numbers from string.

Kushal_Chawda — Fri, 16 Oct 2020 21:46:48 GMT

@HenryFoth_2021 try below

Data: LOAD * where Sequence=4; LOAD *, AutoNumber(Numbers,String) as Sequence where IsNum(Numbers); LOAD String, SubField(String,' ') as Numbers Inline [ String 191 11 23 666 12 34 4 123 2333 56 445 344 55 4 ]; DROP Fields Sequence;

Re: How to keep only numbers from string.

rwunderlich — Sun, 18 Oct 2020 16:44:59 GMT

As others have suggested, SubField(myfield, ' ',5) is the way to go. The problem is the variable number of spaces between numbers. Here's how I normalize multiple spaces to one.

SpaceMap:
Mapping Load repeat(' ', RecNo()+1), ' '
AutoGenerate 20;

and then when I load the number field use the map with a MapSubString.

SubField(MapSubString('SpaceMap',mynum),' ',4)

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

Re: How to keep only numbers from string.

Vegar — Sun, 18 Oct 2020 20:24:02 GMT

Several good suggestions already, but I'll add two more suggestions for you @HenryFoth_2021 .

Solution 1
I notice that even if you have variable amounts of blank spaces, your source look structured. Could it be that we are looking at a fixed position text file, where field1 occupies the four first positions, field 2 position 5-12, field 12-18, etc? If so then you should not read the source with a delimiter, but as a fixed file. On a text file (MySource.txt) that looks like this...

1900 0 0 0 1900 4 45 0 32 2105 8 0 0 2113 7 35 0 44 2164 15 0 0 2181 29 143 0 197 2169 7774 0 0 9944 62 419 0 321

... the syntax would be something like this:

LOAD // [@1:8], // [@9:16], // [@17:19], // [@20:22], [@23:31] as TheNumber, // [@32:36], // [@37:41], // [@42:44], // [@45:n] FROM MySource.txt (fix, utf8, no labels);

Solution 2
You can re-read the field content as a source using using FROM_FIELD. My experience is that when using LOAD FROM_FIELD multiple blanks will be ingored and handled as one. Syntax would be something similar to this.

LOAD @5 as TheNumber FROM_FIELD (MyTable, FieldContainginTableInfo) (txt, utf8, no labels, delimiter is spaces, msq);

Re: How to keep only numbers from string.

HenryFoth_2021 — Mon, 19 Oct 2020 18:10:27 GMT

Thanks all for the replies and possible solutions.

@Vegar , yours worked wonderful.

This community is awesome! Thanks.