Case function using Position function

[Stu412]

Hi there, 

I have a field which needs to return a value based on the contents of other fields around it, so I'm using a CASE statement to achieve this.  Nice and simple, write it once and forget about it.

Case (Code = 1 ; "Income Band A" ; Code = 2 ; "Income Band B")

I want to add something to the CASE statement which evaluates another field based on text contents, so I have tried to use the POSITION command in the CASE statement to make this work, but no luck so far.  Here's what I have:

Case (Code = 1 ; "Income Band A" ; Code = 2 ; "Income Band B"; Position (Description;"TOURIST";1;1) = "Tourist" ; "Tourist")

What I'm trying to do is to evaluate the Description field for the presence of the word 'tourist' anywhere and then have this returned via the case statement.

Any help appreciated, thanks in advance

[ggt667]

 

If you ask for is "" you will get TRUE if the word Tourist is not in that field

Case( Position( Description; "TOURIST"; 1; 1 ) = ""; "Not tourist"; "Tourist" )

I believe what you want to write is: Case(
  Code = 1; "Income Band A";
  Code = 2; "Income Band B";
  Position( Description; "TOURIST"; 1; 1 ) ≠ "" ; "Tourist"
) 

... or the Wim-approach

Case(
  Code = 1; "Income Band A";
  Code = 2; "Income Band B";
  PatternCount( Description; "TOURIST" ) > 0 ; "Tourist"
)

Edited September 8, 2015 by ggt667

[Wim Decorte]

You don't need Position for this but Patterncount()

Also remember that the Case() function will stop at the first test that is true.  So if you want to check for Tourist first then make that  your first test.

[eos]

You don't need Position for this but Patterncount()

And since neither function returns an empty result, but a number, you should also use 

Case
  PatternCount ( Description ; "Tourist" ) ; "Tourist" // = PatternCount ( Description ; "TOURIST" ) ≠ 0
  // ; more test / results
)

rather than = "" [which itself should better be written as IsEmpty ( expression ) ].

Edited September 8, 2015 by eos

[ggt667]

 

rather than = "" [which itself should better be written as IsEmpty ( expression ) ].

When you say something is better, why do you say so? Do you mean that there is higher legibility? Or do you mean that the approach uses less resources if run in a loop 1 billion times? Or some other reason?

Edited September 8, 2015 by ggt667

[Wim Decorte]

And since neither function returns an empty result, but a number, you should also use 

Case
  PatternCount ( Description ; "Tourist" ) ; "Tourist" 

I would prefer to see:

Case(
  PatternCount ( Description ; "Tourist" )  > 0 ; "Tourist" 

That makes it really explicit and more readable (at least to me)

[comment]

 

If you ask for is "" you will get TRUE if the word Tourist is not in that field

Case( Position( Description; "TOURIST"; 1; 1 ) = ""; "Not tourist"; "Tourist" )

Position( Description; "TOURIST"; 1; 1 ) = "" will NEVER return true. The result of Position() is a number. If the searchString is not found, the result will be 0. The correct syntax here would be:

Case ( Position ( Description ; "TOURIST" ; 1 ; 1 ) ; "Tourist" ; "Not tourist"  )

There is no need to compare the result of Position() to any value; the result of 0 will be evaluated as False, any other result will be True.

[Wim Decorte]

When you say something is better, why do you say so? Do you mean that there is higher legibility? Or do you mean that the approach uses less resources if run in a loop 1 billion times? Or some other reason?

I would very strongly recommend against trying to optimize your code for 1 billion iterations if it means sacrificing readability and maintainability of your code.

Even optimizing your code for 1,000 iterations if those thousand iterations are never going to happen...

 

 

[eos]

When you say something is better, why do you say so? Do you mean that there is higher legibility? Or do you mean that the approach uses less resources if run in a loop 1 billion times? Or some other reason?

It's a function provided for this very purpose – and it likely has a higher legibility, though I'd say that is a function of the beholder.

[Stu412]

Patterncount is working as I need it to on this and I've improved the CASE statement to ensure it doesn't stop at the first true result.  Thanks everybody for your help on this one.

[Wim Decorte]

 and I've improved the CASE statement to ensure it doesn't stop at the first true result.

But it will... that's just what the Case() function does, you can't make it do otherwise.  So don't get caught out by it.

[eos]

Patterncount is working as I need it to on this 

It's not really clear what you need:

return a result of either "Band A” or "Band B” or "Tourist”, or ( "Band A” or "Band B” ) and "Tourist”?

If the latter, you'd want to try something like

Case (
  Code = 1 ; "Income Band A" ;
  Code = 2 ; "Income Band B"
) 
& 
Case ( PatternCount ( Description ; "Tourist" ) ; " Tourist" )

btw, depending on the test data, a nice alternative to Case() is Choose():

Choose ( Code - 1 ; "Income Band A" ; "Income Band B" ) // Choose() is zero-based

[ggt667]

I would very strongly recommend against trying to optimize your code for 1 billion iterations if it means sacrificing readability and maintainability of your code.

Even optimizing your code for 1,000 iterations if those thousand iterations are never going to happen...

 

 

I believe more people should measure their solutions in respect of bandwidth distribution; and plan for the solution particularly the pieces that is being looped to spend as little bandwidth as possible, may it be on the CPU, RAM, fiber, FlashDrive, SSD, HDD or whatever media is involved.

Here is a link to an enlightening book on the topic: http://chimera.labs.oreilly.com/books/1230000000545/index.html even though the book is written on the topic of HTTP/1.0 / 1.1 / SPDY / 2.0 everything in the book  applies to all applications regardless.

Edited September 8, 2015 by ggt667

[Wim Decorte]

To be balanced against: http://c2.com/cgi/wiki?PrematureOptimization