QlikView App Dev

[HenryFoth_2021]

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.

[dplr-rn]

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]

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]

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]

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 · Accepted solution]

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]

Thanks all for the replies and possible solutions.

@Vegar , yours worked wonderful.

This community is awesome! Thanks.