Jump to content

Case function using Position function


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

Recommended Posts

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

Link to comment
Share on other sites

 

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

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

 

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

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)

Link to comment
Share on other sites

 

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.

Link to comment
Share on other sites

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...

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

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

This topic is 3100 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.