Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

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

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

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

7 Replies
Highlighted
Partner
Partner

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

Highlighted
Partner
Partner

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

 

Highlighted
Specialist II
Specialist II

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)

Highlighted
MVP
MVP

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

 

 

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
Partner
Partner

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

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

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Highlighted
Contributor II
Contributor II

Thanks all for the replies and possible solutions.

@Vegar , yours worked wonderful.

This community is awesome! Thanks.