Filtering down to one word

[Heathbo]

I have a filed that filters values from many different fields. Some of the values are, Green Hunter, Green Olive, Green Apple, Blue Sky, Blue Baby, Blue Water. Sometimes different versions of Green will show up in this field, along with different version of Blue. What I need to do is create another calculation field that filters all versions of Green and calls it Green and all the versions of Blue and calls it Blue. How would I go about creating the second calculation field? Any help you can give is much appreciated.

[Heathbo]

I have a filed that filters values from many different fields. Some of the values are, Green Hunter, Green Olive, Green Apple, Blue Sky, Blue Baby, Blue Water. Sometimes different versions of Green will show up in this field, along with different version of Blue. What I need to do is create another calculation field that filters all versions of Green and calls it Green and all the versions of Blue and calls it Blue. How would I go about creating the second calculation field? Any help you can give is much appreciated.

[Heathbo]

I have a filed that filters values from many different fields. Some of the values are, Green Hunter, Green Olive, Green Apple, Blue Sky, Blue Baby, Blue Water. Sometimes different versions of Green will show up in this field, along with different version of Blue. What I need to do is create another calculation field that filters all versions of Green and calls it Green and all the versions of Blue and calls it Blue. How would I go about creating the second calculation field? Any help you can give is much appreciated.

[BobWeaver]

You can use a calculation like this:

Case(

Position(Inputfield;"Green";1;1);"Green";

Position(Inputfield;"Blue";1;1);"Blue";

"")

You can extend the formula for other colors too.

[BobWeaver]

You can use a calculation like this:

Case(

Position(Inputfield;"Green";1;1);"Green";

Position(Inputfield;"Blue";1;1);"Blue";

"")

You can extend the formula for other colors too.

[BobWeaver]

You can use a calculation like this:

Case(

Position(Inputfield;"Green";1;1);"Green";

Position(Inputfield;"Blue";1;1);"Blue";

"")

You can extend the formula for other colors too.

[LaRetta]

Hi Bob, quick question?

Could you explain the difference between using Position() and PatternCount() on this calculation? It's small enough that it makes little difference here but I thought PatternCount() wouldn't need to evaluate like Position() since we can use it as boolean. Is there a difference?

Case(

PatternCount(Inputfield; "Blue"); "Blue";

PatternCount(Inputfield "Green"); "Green";

)

I find myself bumping into these two functions in many instances and debating which is the most efficient (least evaluations) when both will do the job. I'd like a clearer perspective if you would be so kind. Thank you!

LaRetta

[LaRetta]

Hi Bob, quick question?

Could you explain the difference between using Position() and PatternCount() on this calculation? It's small enough that it makes little difference here but I thought PatternCount() wouldn't need to evaluate like Position() since we can use it as boolean. Is there a difference?

Case(

PatternCount(Inputfield; "Blue"); "Blue";

PatternCount(Inputfield "Green"); "Green";

)

I find myself bumping into these two functions in many instances and debating which is the most efficient (least evaluations) when both will do the job. I'd like a clearer perspective if you would be so kind. Thank you!

LaRetta

[LaRetta]

Hi Bob, quick question?

Could you explain the difference between using Position() and PatternCount() on this calculation? It's small enough that it makes little difference here but I thought PatternCount() wouldn't need to evaluate like Position() since we can use it as boolean. Is there a difference?

Case(

PatternCount(Inputfield; "Blue"); "Blue";

PatternCount(Inputfield "Green"); "Green";

)

I find myself bumping into these two functions in many instances and debating which is the most efficient (least evaluations) when both will do the job. I'd like a clearer perspective if you would be so kind. Thank you!

LaRetta

[BobWeaver]

I now use Position() because Queue got mad at me for my inefficient use of PatternCount().

Actually, either one will work in this case because all we want to get is a zero if the text is not found, and any number if it is found. Both functions will do this. Position() is (theoretically) more efficient because it only needs to examine the text up to the point where the first match is found and then it can stop. PatternCount() must count all occurrences, so it must examine the entire text string. Personally, I like to use PatternCount() because I'm too lazy to type in those extra parameters required by Position().

[BobWeaver]

I now use Position() because Queue got mad at me for my inefficient use of PatternCount().

Actually, either one will work in this case because all we want to get is a zero if the text is not found, and any number if it is found. Both functions will do this. Position() is (theoretically) more efficient because it only needs to examine the text up to the point where the first match is found and then it can stop. PatternCount() must count all occurrences, so it must examine the entire text string. Personally, I like to use PatternCount() because I'm too lazy to type in those extra parameters required by Position().

[BobWeaver]

I now use Position() because Queue got mad at me for my inefficient use of PatternCount().

Actually, either one will work in this case because all we want to get is a zero if the text is not found, and any number if it is found. Both functions will do this. Position() is (theoretically) more efficient because it only needs to examine the text up to the point where the first match is found and then it can stop. PatternCount() must count all occurrences, so it must examine the entire text string. Personally, I like to use PatternCount() because I'm too lazy to type in those extra parameters required by Position().

[LaRetta]

Oh dear, Bob!

JT explained this once before to me but I lost the explanation.

For some odd reason, it doesn't want to stick in my brain because it seems that it should be reversed; probably because PatternCount() looks like it takes less evaluations (which is probably because it's easier to write).

Okay. Position() and I shall become friends then. And length (or time) writing a calculation has nothing to do with it's efficiency. Much appreciated!

LaRetta

[LaRetta]

Oh dear, Bob!

JT explained this once before to me but I lost the explanation.

For some odd reason, it doesn't want to stick in my brain because it seems that it should be reversed; probably because PatternCount() looks like it takes less evaluations (which is probably because it's easier to write).

Okay. Position() and I shall become friends then. And length (or time) writing a calculation has nothing to do with it's efficiency. Much appreciated!

LaRetta

[LaRetta]

Oh dear, Bob!

JT explained this once before to me but I lost the explanation.

For some odd reason, it doesn't want to stick in my brain because it seems that it should be reversed; probably because PatternCount() looks like it takes less evaluations (which is probably because it's easier to write).

Okay. Position() and I shall become friends then. And length (or time) writing a calculation has nothing to do with it's efficiency. Much appreciated!

LaRetta

[Lee Smith]

Hi Bob,

LOL, Thanks for this:

I now use Position() because Queue got mad at me .....

Yep, me too.

[Lee Smith]

Hi Bob,

LOL, Thanks for this:

I now use Position() because Queue got mad at me .....

Yep, me too.

[Lee Smith]

Hi Bob,

LOL, Thanks for this:

I now use Position() because Queue got mad at me .....

Yep, me too.

[Fenton]

Position is faster than PatternCount. I still use PatternCount however, when it's only one test on a small amount of text, because, as you say, it is shorter and more intuitive.

PatternCount has to count all occurrennces of the text. Position only has to find the 1st occurrence.

[Fenton]

Position is faster than PatternCount. I still use PatternCount however, when it's only one test on a small amount of text, because, as you say, it is shorter and more intuitive.

PatternCount has to count all occurrennces of the text. Position only has to find the 1st occurrence.

[Fenton]

Position is faster than PatternCount. I still use PatternCount however, when it's only one test on a small amount of text, because, as you say, it is shorter and more intuitive.

PatternCount has to count all occurrennces of the text. Position only has to find the 1st occurrence.

[Ugo DI LUCA]

Hmm...

Yes Position is quicker than PatternCount, but is it really the circunstance here.

The title was : Filtering down to one word.

And the values seem to be ordered with "Color<Space>Detail"

So could it be that LeftWords(MyValue;1) is even quicker ?

[Ugo DI LUCA]

Hmm...

Yes Position is quicker than PatternCount, but is it really the circunstance here.

The title was : Filtering down to one word.

And the values seem to be ordered with "Color<Space>Detail"

So could it be that LeftWords(MyValue;1) is even quicker ?

[Ugo DI LUCA]

Hmm...

Yes Position is quicker than PatternCount, but is it really the circunstance here.

The title was : Filtering down to one word.

And the values seem to be ordered with "Color<Space>Detail"

So could it be that LeftWords(MyValue;1) is even quicker ?

[-Queue-]

I never got mad at anyone for using PatternCount. I merely expressed that it is more efficient to use Position when the text being tested is large. If it's only a few words or values, the difference is probably negligible. If it's a large text field or a function like DatabaseNames, Position can be much more efficient.

So sue me for trying to help make your solutions faster.

I think Ugo wins the argument on this one though. LeftWords is most efficient in this situation.

[-Queue-]

I never got mad at anyone for using PatternCount. I merely expressed that it is more efficient to use Position when the text being tested is large. If it's only a few words or values, the difference is probably negligible. If it's a large text field or a function like DatabaseNames, Position can be much more efficient.

So sue me for trying to help make your solutions faster.

I think Ugo wins the argument on this one though. LeftWords is most efficient in this situation.

[-Queue-]

I never got mad at anyone for using PatternCount. I merely expressed that it is more efficient to use Position when the text being tested is large. If it's only a few words or values, the difference is probably negligible. If it's a large text field or a function like DatabaseNames, Position can be much more efficient.

So sue me for trying to help make your solutions faster.

I think Ugo wins the argument on this one though. LeftWords is most efficient in this situation.

[LaRetta]

Hey JT!!!

"So sue me for trying to help make your solutions faster."

If I could sue you for all the wonderful help you've given me, I'd be a millionaire! You just keep right on pointing it out to me - it's VERY important!!

We all just love to tease you about it because you're so darned GOOD at efficiency. It's one of your endearing qualities and if you stopped, I'd be sorely disappointed. In fact, when I post a calculation and you DON'T respond with a better way, I'm always a bit disappointed.

As for LeftWords()... yeah, I considered it but experience is teaching me that the User or Management will change the rules, ie, introduce Robin's Egg Blue and then wonder why the calc broke.

LaRetta

[LaRetta]

Hey JT!!!

"So sue me for trying to help make your solutions faster."

If I could sue you for all the wonderful help you've given me, I'd be a millionaire! You just keep right on pointing it out to me - it's VERY important!!

We all just love to tease you about it because you're so darned GOOD at efficiency. It's one of your endearing qualities and if you stopped, I'd be sorely disappointed. In fact, when I post a calculation and you DON'T respond with a better way, I'm always a bit disappointed.

As for LeftWords()... yeah, I considered it but experience is teaching me that the User or Management will change the rules, ie, introduce Robin's Egg Blue and then wonder why the calc broke.

LaRetta

[LaRetta]

Hey JT!!!

"So sue me for trying to help make your solutions faster."

If I could sue you for all the wonderful help you've given me, I'd be a millionaire! You just keep right on pointing it out to me - it's VERY important!!

We all just love to tease you about it because you're so darned GOOD at efficiency. It's one of your endearing qualities and if you stopped, I'd be sorely disappointed. In fact, when I post a calculation and you DON'T respond with a better way, I'm always a bit disappointed.

As for LeftWords()... yeah, I considered it but experience is teaching me that the User or Management will change the rules, ie, introduce Robin's Egg Blue and then wonder why the calc broke.

LaRetta

[-Queue-]

True, if we base it on only the sample colors, then it will work; if there are more complicated examples, then PatternCount is the way to go. Of course, isn't this how it always is? We cannot determine max efficiency from a few samples when they may be very different than other ones. We can only suggest possible paths unless provided with more detailed information. Perhaps we should include a note in the forum rules to be as explicit as possible, alert us of any discrepancies to the example(s), etc. But it's not as if anyone would read those any more often than they read about not double-posting now. Oh well, one can dream...

If I don't respond with a better solution or at least some other option, then either my brain is not functioning optimally, I'm swamped with real work (grr), I'm completely apathetic to such things at the moment (i.e. I'm playing a video game, watching a movie, or reading a Dean Koontz book ), or the most recently-suggested solutions look fine to me. I'll try to remember to comment not only when I think there's a better way, but also when it looks like you've already nailed it. I need to work on giving positive feedback more often anyway, so I don't get a reputation for only yelling negatively.

[-Queue-]

True, if we base it on only the sample colors, then it will work; if there are more complicated examples, then PatternCount is the way to go. Of course, isn't this how it always is? We cannot determine max efficiency from a few samples when they may be very different than other ones. We can only suggest possible paths unless provided with more detailed information. Perhaps we should include a note in the forum rules to be as explicit as possible, alert us of any discrepancies to the example(s), etc. But it's not as if anyone would read those any more often than they read about not double-posting now. Oh well, one can dream...

If I don't respond with a better solution or at least some other option, then either my brain is not functioning optimally, I'm swamped with real work (grr), I'm completely apathetic to such things at the moment (i.e. I'm playing a video game, watching a movie, or reading a Dean Koontz book ), or the most recently-suggested solutions look fine to me. I'll try to remember to comment not only when I think there's a better way, but also when it looks like you've already nailed it. I need to work on giving positive feedback more often anyway, so I don't get a reputation for only yelling negatively.

[-Queue-]

True, if we base it on only the sample colors, then it will work; if there are more complicated examples, then PatternCount is the way to go. Of course, isn't this how it always is? We cannot determine max efficiency from a few samples when they may be very different than other ones. We can only suggest possible paths unless provided with more detailed information. Perhaps we should include a note in the forum rules to be as explicit as possible, alert us of any discrepancies to the example(s), etc. But it's not as if anyone would read those any more often than they read about not double-posting now. Oh well, one can dream...

If I don't respond with a better solution or at least some other option, then either my brain is not functioning optimally, I'm swamped with real work (grr), I'm completely apathetic to such things at the moment (i.e. I'm playing a video game, watching a movie, or reading a Dean Koontz book ), or the most recently-suggested solutions look fine to me. I'll try to remember to comment not only when I think there's a better way, but also when it looks like you've already nailed it. I need to work on giving positive feedback more often anyway, so I don't get a reputation for only yelling negatively.

[LaRetta]

I just realized my newly established theory from a month ago (plan ahead for contingency and factor into my calculations) is incorrect also. Because if we always plan for 'what ifs' our solutions might not be optimum.

I think I'll modify my theory to more of, "If you are SURE it will always start with ... then ... BUT if it ever changes ..." etc etc. I'm also finding myself wanting to get everything in writing that we agree upon because it's come back and bit me a few times.

And it isn't your job to run around behind us making sure we post the best answers but, oh, I dearly love to have my work fine-tuned. I recognize that most people would need to pay big bucks for the kind of consultation we all receive for free. I doubt ANY of my calculations would ever be optimum, JT, unless it's a simple If().

"... I'm playing a video game, watching a movie, or reading a Dean Koontz book "

Intensity. Best book ever. My kinda book!! Or you could be makin' music! God, can you sing!

LaRetta

[LaRetta]

I just realized my newly established theory from a month ago (plan ahead for contingency and factor into my calculations) is incorrect also. Because if we always plan for 'what ifs' our solutions might not be optimum.

I think I'll modify my theory to more of, "If you are SURE it will always start with ... then ... BUT if it ever changes ..." etc etc. I'm also finding myself wanting to get everything in writing that we agree upon because it's come back and bit me a few times.

And it isn't your job to run around behind us making sure we post the best answers but, oh, I dearly love to have my work fine-tuned. I recognize that most people would need to pay big bucks for the kind of consultation we all receive for free. I doubt ANY of my calculations would ever be optimum, JT, unless it's a simple If().

"... I'm playing a video game, watching a movie, or reading a Dean Koontz book "

Intensity. Best book ever. My kinda book!! Or you could be makin' music! God, can you sing!

LaRetta