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

Thank you but if you see Zip1 & Zip2 fields are both same.
In your image, I'd need 71275 as Zip1 & 75635 as Zip2.
Also Zip1 & Zip2 should only be these numbers. Not contain any alphabets.

 

Edit:

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

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

 

I replaced 1 with 2 with zip2 it worked but some of it still has values such as CPT, ) etc

MK9885
Master II
Master II
Author

Is there a way to keep "TC" after a number?
some of the numbers have TC, like 74183-TC
so Zip1 gives me just 74183 but I'd need 74183-TC