Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
Delimitator is not fixed
it can be ',' or '-' or just blank space
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
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)
Doesn't work for me...@Fabian.
Did you see my attached file?
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)
@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 ];
@Kushal_Chawda
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.
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
@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?
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"
] ;
this solution assumes that the State appears only once and it positioned right before the zip codes