Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Advanced nested SubField

Hello! I have a problem with my subfield function even after browsing through the forum. I hope you can help me.

I have a column with addresses which looks like this:

somestreet 123, city     but sometimes it will look like this:

some street name 123, city

Now what I want to do is to get rid of the number so in the end my rows will look like:

somestreet, city

some street name, city  and so on.

I have tried nesting my subfields but I cannot seem to get rid of the number. The problem is the various amount of blank spaces in the street name, sometimes there is none, sometimes there is 1 or more.

The furthest I have come is this:

SubField(SubField([FieldName], ' ', 1), ',', 1) & SubField([FieldName], ',', 2) AS [FieldName] but that of course results in the street names with spaces to be cut off and only show the beginning.

'somestreet123, city' works fine and becomes 'somestreet city' BUT

'some street name 123, city' becomes 'some city' which is wrong.

Any ideas?

Thanks a lot in advance!

5 Replies
petter
Partner - Champion III
Partner - Champion III

You can make use of the PurgeChar()-function to first get rid of the numbers. Then you can split the fields into two by using SubField() and lastly you can again use PurgeChar()-function to get rid of the spaces in the first field.

maxgro
MVP
MVP

maybe

purgechar(SubField(FieldName, ',',1),'0123456789') & ' ' & SubField(FieldName, ',',2)

MarcoWedel

maybe just

PurgeChar(YourField,'0123456789')

could be enough?

regards

Marco

Not applicable
Author

Hello again everyone and thank you for your input MarcoWedelpetter-s

maxgro

Your solution got me very close although there is one problem left. Some of the house numbers actually have a letter in them (sorry for not specifying that earlier). So they are like:

some street name 123A, city

but if I put the letter into the purgechar then of course all streets that start with that letter also disappear.

Is there a way to specify the purgechar with something like

"if(house number contains a letter, purgechar([field], '0123456789ABC'), purgechar([field], '0123456789'))" ?

MarcoWedel

Hi,

maybe like this:

QlikCommunity_Thread_187405_Pic1.JPG

tabAddress:

LOAD Replace(Concat(Address,' ',IterNo),' ,',',') as Address

Where not FindOneOf(Address,'0123456789')

Group By RecNo;

LOAD SubField(Replace(Address,',',' ,'),' ',IterNo()) as Address,

    RecNo() as RecNo,

    IterNo() as IterNo

Inline [

Address

"somestreet 123, city"

"some street name 123, city"

"some different street name 123A, city"

"some other street name 123B, city"

]

While IterNo()<=SubStringCount(Replace(Address,',',' ,'),' ')+1;

hope this helps

regards

Marco