Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
MK9885
Master II
Master II

Get only number from a string after a particular letters or symbol

Hi,

I have a string coming in from DB as an example

John Doe address is 1234 Street ABC City NY 98756 & 45612

Here I'd just like to get numbers after NY, but not the symbol '&' just numerics

Would like to create 2 fields out of above string

1st field should have 98756

2nd field should have 45612

Tried len & keepchar, doesn't work or maybe I'm missing something.

Any help is appreciated @sunny_talwar  or anyone else..

 

Labels (1)
21 Replies
MK9885
Master II
Master II
Author

Delimitator is not fixed 
it can be ',' or '-' or just blank space

MK9885
Master II
Master II
Author

Please see attached field
Need Numbers only after CPT without any delimiter
First number should be Field 1

second is Field 2 & third is Field 3 

QFabian
Specialist II
Specialist II

this works for me :

=subfield(replace(purgechar(trim(mid(yourfield,findoneof( vA, 'NY')+2)), '&-,'), ' ', ' '), ' ', 1)

=subfield(replace(purgechar(trim(mid(yourfield,findoneof( vA, 'NY')+2)), '&-,'), ' ', ' '), ' ', 2)

QFabian
MK9885
Master II
Master II
Author

Doesn't work for me...@Fabian.

Did you see my attached file?

QFabian
Specialist II
Specialist II

this work for me :

=subfield(replace(purgechar(trim(mid(vA,findoneof( yourfield, 'NY')+2)), '&-,'), ' ', ' '), ' ', 1)

=subfield(replace(purgechar(trim(mid(vA,findoneof( yourfield, 'NY')+2)), '&-,'), ' ', ' '), ' ', 2)

QFabian
Kush
MVP
MVP

@MK9885  try below

LOAD *,
trim(subfield(SubField(Text,'NY',-1),'&',1)) as Field1,
trim(subfield(SubField(Text,'NY',-1),'&',2)) as Field2
Inline [
Text
John Doe address is 1234 Street ABC City NY 98756 & 45612 ];

MK9885
Master II
Master II
Author

@Kush 
Please see attached data file on top of this page
test field can be CPT or SCHS
delimiter can be , & - or just blank space

Thanks. 

rwunderlich

Ok. So what is constant is "NY" (I take it those that don't have NY you don't care about).  After NY is 0 to 2 numbers. Those numbers  may be separated by "-", ",", "&" or space, plus any number of spaces.  Here's a script that may do the trick (another alternative is to use a regular expression if you or someone else can code the regex to do the parsing).  

SpaceMap:
Mapping LOAD Repeat(' ', RecNo()+1), ' ' AutoGenerate 20;


DelimMap:
Mapping LOAD delim, ' '
Inline [

delim
&
","
-
]
;

LOAD
  *,
  SubField(zips, ' ', 1) as Zip1,
  SubField(zips, ' ', 2) as Zip2,
;
LOAD
  source,
  MapSubString('SpaceMap'
    ,
MapSubString('DelimMap'
      ,
Trim(SubField(source, 'NY', 2))
  ))
as zips
INLINE [

source
"John Doe address is 1234 Street ABC City NY 98756 & 45612"
"John Doe address is 1234 Street ABC City NY 98756&45612"
"John Doe address is 1234 Street ABC City NY 98756,45612"
"John Doe address is 1234 Street ABC City NY 98756, 45612"
"John Doe address is 1234 Street ABC City NY 98756 - 45612"
"John Doe address is 1234 Street ABC City NY 98756-45612"
"John Doe address is 1234 Street ABC City NY 98756  -  45612"
"John Doe address is 1234 Street ABC City NY 98756 45612"
"John Doe address is 1234 Street ABC City NY "
"Jane Doe address is 1234 Street ABC City WI "
"Jane Doe address is 1234 Street ABC City WI 12345 54321"
"John Doe address is 1234 Street ABC City NY 98756"
] ;

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

 

MK9885
Master II
Master II
Author

@rwunderlich 
Thanks for the reply..
tried your script, didn't work..
I have attached the field where I just need numbers after CPT (which are 5 digits)
some times I have two 5 digits numbers. 
1st 5 digit Number should be field 1 & 2nd 5 digit number should be field 2.

Example:
CT CTA Aorta/Bilat Iliofem Runoff (C-/C+) CPT 71275, 75635

Field1
71275
Field2
75635
Some times I have only Field1 or I have 3 fields
I hope that makes sense? 

edwin
Specialist III
Specialist III

follow on to Rob's idea, first identify the state.  then get the right of the state then parse that:

subfield(text, state,-1) is the string that has the two zip codes;  then parse that (you need to trim it first)

load source ,

replace(replace(replace(upper(source),'&',' '),',',' '),'-',' ') as cleaneduptxt, //removed special characters seprating the two zip codes - change to space

replace(replace(replace(
subfield(rtrim(KeepChar(upper(source),'ABCDEFGHIJKLMANOPQRSTUVWXYZ ')),' ',-1)
,'&',' '),',',' '),'-',' ') as State, // this is the state or last string before the two zip codes

//this selects the first number after the last alpahbetic string (can be NY, VA, CA, CPT, etc..
subfield(ltrim(rtrim(SubField(

//this is the cleaned up text
replace(replace(replace(upper(source),'&',' '),',',' '),'-',' '),

//this is the state - using it as the delimeter
replace(replace(replace(
subfield(rtrim(KeepChar(upper(source),'ABCDEFGHIJKLMANOPQRSTUVWXYZ ')),' ',-1)
,'&',' '),',',' '),'-',' ')

,-1))),' ',1)as Zip1,

subfield(ltrim(rtrim(SubField(

replace(replace(replace(upper(source),'&',' '),',',' '),'-',' '),

replace(replace(replace(
subfield(rtrim(KeepChar(upper(source),'ABCDEFGHIJKLMANOPQRSTUVWXYZ ')),' ',-1)
,'&',' '),',',' '),'-',' ')

,-1))),' ',1)as Zip2

INLINE [
source
"John Doe address is 1234 Street ABC City NY 98756 & 45612"
"John Doe address is 1234 Street ABC City NY 98756&45612"
"John Doe address is 1234 Street ABC City NY 98756,45612"
"John Doe address is 1234 Street ABC City NY 98756, 45612"
"John Doe address is 1234 Street ABC City NY 98756 - 45612"
"John Doe address is 1234 Street ABC City NY 98756-45612"
"John Doe address is 1234 Street ABC City NY 98756 - 45612"
"John Doe address is 1234 Street ABC City NY 98756 45612"
"John Doe address is 1234 Street ABC City NY "
"Jane Doe address is 1234 Street ABC City WI "
"Jane Doe address is 1234 Street ABC City WI 12345 54321"
"John Doe address is 1234 Street ABC City NY 98756"
"CT CTA Aorta/Bilat Iliofem Runoff (C-/C+) CPT 71275, 75635"
] ;

edwin_0-1605196686989.png

this solution assumes that the State appears only once and it positioned right before the zip codes