Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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
MVP
MVP

Re: Advanced nested SubField

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.

MVP
MVP

Re: Advanced nested SubField

maybe

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

Re: Advanced nested SubField

maybe just

PurgeChar(YourField,'0123456789')

could be enough?

regards

Marco

Highlighted
Not applicable

Re: Advanced nested SubField

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'))" ?

Re: Advanced nested SubField

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