Case function and AND statements

[Nicnacs]

Hello

I have a database which takes address fields and combines them into one address field (for our website so we don't have to do asp stuff with lots of address lines!). So we have fields:

address1

address2

address3

address4

postcode

I wrote a simple CASE calculation to return results if either of lines 2 or 3 are blank (lines 1 and 4 are always full) but can't figure out what to do if BOTH 2 and 3 are blank.

You end up with addresses that look a little like this:

My house,, This street, BR2 6PA

The commas look messy.

I've tried using AND, I did a search and looked at a previous post which suggested using not IsEmpty but it's not happening! Here's what I've got:

Case (

not IsEmpty ( address3) and not IsEmpty ( address2) ; address1&", "&address4&", "&postcode;

address3=""; address1&", "&address2&", "&address4&", "&postcode;

address2=""; address1&", "& address3&", "&address4&", "&postcode;

address1&", "&address2&", "& address3&", "&address4&", "&postcode)

Basically everything works, it just ignores the whole not empty stuff.

Any thoughts?

Thanks

Nicole

[Zero Tolerence]

Case(

IsEmpty(address3) and IsEmpty (Address2); add1 & "," & add4 & "," & postcode;

IsEmpty(add3); add1 & "," & add2 & "," & add4 & "," & postcode;

IsEmpty(add2); add1 & "," & add3 & "," & add4 & "," & postcode;

add1 & "," & add2 & "," & add3 & "," & add4 & "," & postcode)

Not IsEmpty is saying if add2 and add3 AREN'T EMPTY, take the not off like in my code above and your saying if they both ARE empty.

Not IsEmpty means /= ""

IsEmpty means = ""

[Lee Smith]

Try this and see if it is what you want

Case(not IsEmpty(address1); address1) &

Case(not IsEmpty(address2); "¶" & address2) &

Case(not IsEmpty(address3); "¶" & address3) &

Case(not IsEmpty(address4); "¶" & address4)

I forgot to mention that if you are wanting commas or periods instead of paragraphs, then subsitute the comma or periond for the ¶ in the calculation.

HTH

Lee

[Søren Dyhr]

If Lee's suggestion gives the desired result, am I not sure - it's going to be the way I would do it....

LeftWords(

Substitute ( Address1 & "¶" & Address2 & "¶" & Address3 & "¶" & Address4 ; ["¶¶";"¶"];["¶¶¶";"¶"])

;99999)

You might wonder why the LeftWords( are doing, it's simply stripping the last pilcrow of if Address4 is empty - no matter how many words exists within reason here 99999.

--sd

[Nicnacs]

Thanks! After a bit of fiddling, the first solution worked. Code is now:

Case(

IsEmpty(address3) and IsEmpty (address2); address1 & ", " & address4 & ", " & postcode; IsEmpty(address3); address1 & ", " & address2 & ", " & address4 & ", " & postcode;

IsEmpty (address2); address1 & ", " & address3 & ", " & address4 & ", " & postcode;

address1 & ", " & address2 & ", " & address3 & ", " & address4 & ", " & postcode )

[Søren Dyhr]

Well "postcode" is common, so why not put it outside the paranthesis??? Well it seems like "address4" could be set outside as well - however in the following is it only "postcode" that is put outside.

Case(



IsEmpty(address3) and IsEmpty (address2); address1 & ", " & address4 & ", " & postcode; IsEmpty(address3); address1 & ", " & address2 & ", " & address4 & ", " & postcode; 

IsEmpty (address2); address1 & ", " & address3 & ", " & address4 & ", " & postcode;

address1 & ", " & address2 & ", " & address3 & ", " & address4 & ", " & postcode ) 

However does it still give the same result as: 

LeftWords(

Substitute ( Address1 & ", " & Address2 & ", " & Address3 & ", " & Address4 ; [", , ";", "];[", , , ";", "])

;99999) & ", " & postcode

Why does it make sence? Your method uses 5 functions that each and every single of them have a time of execution to evolve. While mine is down to 2 functions total, which is why I consider it in the first place as an optimization. So a Case-statement with AND's isn't necessarily whats required, although you mind tells you so!

I attach my template to investigation!

--sd

test.zip

[bruceR]

substitute(address1 &", " & address2 & ", " & address3 & ", " & address4 & ", " & postcode; [", , , "; ", "] ; [", , "; ", "])

Edited March 31, 2006 by Guest

[Lee Smith]

Hi Bruce,

I modified your calculation to add the Address3 too.

Here is a comparison of the three calculations:

--sd

LeftWords(

Substitute ( address1 & ", " & address2 & ", " & address3 & ", " & address4 ; [", , ";", "];[", , , ";", "])

;99999) & ", " & postcode

BruceR

Substitute(address1 &", " & address2 & ", " & address3 & ", " & address4 &", " & postcode; ",,"; ",")

Mine

Case(not IsEmpty(address1); address1) &

Case(not IsEmpty(address2); ", " & address2) &

Case(not IsEmpty(address3); ", " & address3) &

Case(not IsEmpty(address4); ", " & address4) &

Case(not IsEmpty(postcode); ", " & postcode)

This gives [color:green]Nicnacs at least three choices.

Lee

[Søren Dyhr]

Yes the 99999 thing only occures as a problem if it's pilcrows, and Lee you have a point with the tripple comma, but the algorithm goes

2

2

5

8

etc (well in opposite order actually)

...one single substitution isn't enough, worst case is 3 empties that produces which gives 4 succesive commas - since both address1 and postcode always have values - it makes no difference if we put:

& ", " & postcode

...outside the parantesis. Because you can't zap tripple a thingy if a single or double also might occure.

So the optimal must be either Bruce's second attempt or the more algorithm strict:

substitute(address1 &", " & address2 & ", " & address3 & ", " & address4 & ", " & postcode; [", , "; ", "] ; [", , "; ", "])

...although it's difference might be neglectable, speedwise until we deal with a scripted replace beyond 40000 records perhaps.

--sd

[comment]

I believe the algorithm steps are 2, 2, 3, 6, 21, 231 ...

In any case, I'd thought the algorithm was made obsolete when TrimAll() became available.

[Søren Dyhr]

Yes your math is way better than mine, although you says it's your brother who... I think that it your case have rubbed considerably off

But then in this case can't you take it for granted that no exsists in an address so substitute( each comma to a might go wrong. Take a address part as "Balmoral Terrasses" ...not much use of TrimAll( here if you need to hover the text for double or tripple commas - or??

--sd

[comment]

I cannot find it at the moment, but IIRC it is Bob Weaver's math.

As for the spaces, I thought it would be obvious:

Let ( [

text = field1 & ¶ & field2 & ¶ & field3 & ¶ & field4 ;

separator = ", "

] ;

Substitute (

TrimAll (

Substitute (

TrimAll ( text ; 0 ; 0 ) ;

[ " " ; "§" ] ; [ ¶ ; " "] ) ;

0 ; 0 ) ;

[ " " ; separator ] ; [ "§" ; " " ] )

)

---

Here there's an assumption of no CR's in fields; but that too can be allowed by using another temp separator instead of ¶.

Edited March 31, 2006 by Guest

[Søren Dyhr]

But do you think it's faster than:

substitute(address1 &", " & address2 & ", " & address3 & ", " & address4 & ", " & postcode; [", , "; ", "] ; [", , "; ", "])

If so could you expalin why??

--sd

[comment]

I don't know about faster - but it is GENERAL. You can concatenate ANY number of fields, and it will handle them all with just the 4 substitutions. All the other alternatives require adjustments as the number of fields goes up.

[Søren Dyhr]

Hmm... the debate a few days ago was a discussion, was pointing in the opposite direction....

Taylored versus "One size fits all" ...but there is also a Emanuel Kant vs. Hobbes in it. Is it really worth to be drilled in "...the classics" when all that matters is appearances as being swift in adapting to current trend.

. But compulsory emancipation via lifelong learning is a contradiction in terms. Ivan Illich and Etienne Verne predicted such an outcome when they argued that lifelong learning would become "not the symbol of our unfinished development, but a guarantee of our permanent inadequacy" and would constantly reassign learners to their place in a meritocracy

...snipped from: http://72.14.203.104/search?q=cache:jdSoZ3Nds-AJ:www.erill.uni-bremen.de/lios/plenary/coffield.html+%22not+the+symbol+of+our+unfinished%22&hl=da&lr=&client=firefox-a&strip=1

Where it not might rest forever ...so, hurry up and read the problems in thinking "...If just I'm up on the beat with agility in the newest version of Windows"

And you really have a timeslot available read this:

http://www.wwnorton.com/catalog/fall99/sennett.htm

Should a certain drilling in well established agorithms ...be considered or should we all be "Dedicated followers of fashion"??

--sd

[comment]

Well, it's a question of balance (like everything in life). I believe hardcoding a solution's STRUCTURE is not quite the same as limiting a calculation to the currently known requirements. A change in requirements is (usually) a reasonable expectation. A change in structure is a new solution altogether.

If you're certain that the requirements are not going to change, then by all means, go for speed. But it's not going to be very helpful to other readers of the thread.