Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
HenryFoth_2021
Contributor II
Contributor II

How to keep only numbers from string.

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:

HenryFoth_Sabre_0-1602870662189.png

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

Any suggestions?

Thanks in advance.

1 Solution

Accepted Solutions
Vegar
MVP
MVP

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);

 

View solution in original post

7 Replies
dplr-rn
Partner - Master III
Partner - Master III

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

benvatvandata
Partner - Creator II
Partner - Creator II

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];

 

jwjackso
Specialist III
Specialist III

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)

Kushal_Chawda

@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;

 

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Vegar
MVP
MVP

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);

 

HenryFoth_2021
Contributor II
Contributor II
Author

Thanks all for the replies and possible solutions.

@Vegar , yours worked wonderful.

This community is awesome! Thanks.