Jump to content

Case function and AND statements


This topic is 6570 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites


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 = ""

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 )

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Edited by Guest
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

But do you think it's faster than:

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

If so could you expalin why??

--sd

Link to comment
Share on other sites

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 :jester: 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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6570 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.