topic Re: Advanced nested SubField in QlikView

Advanced nested SubField

Mon, 19 Oct 2015 15:19:27 GMT

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!

Re: Advanced nested SubField

petter — Mon, 19 Oct 2015 16:10:55 GMT

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.

Re: Advanced nested SubField

maxgro — Mon, 19 Oct 2015 18:11:41 GMT

maybe

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

Re: Advanced nested SubField

MarcoWedel — Mon, 19 Oct 2015 21:56:04 GMT

maybe just

PurgeChar(YourField,'0123456789')

could be enough?

regards

Marco

Re: Advanced nested SubField

Tue, 20 Oct 2015 06:32:59 GMT

Hello again everyone and thank you for your input ‌ MarcoWedel‌ petter-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

MarcoWedel — Wed, 21 Oct 2015 21:28:44 GMT

Hi,

maybe like this:

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