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.

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.

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.

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.

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.

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.

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

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

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

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

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

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

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

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

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

Hi Bob,

LOL, Thanks for this:

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

Yep, me too.

Hi Bob,

LOL, Thanks for this:

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

Yep, me too.

Hi Bob,

LOL, Thanks for this:

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

Yep, me too.

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.

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.

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.

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 ?

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 ?

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 ?

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.

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.

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.

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

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

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

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.

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.

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.

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

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

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

Lightning, False Memory, Odd Thomas, Phantoms, Strangers--I have too many favorites to quantify any as the best book ever. The man does need to stop writing until I have a chance to catch up on the last 40 years though. Sheesh!

If I'm making music now, it's only on the drums. I have no inclination to sing after a few hours of doing that. Maybe someday I will again though...

Lightning, False Memory, Odd Thomas, Phantoms, Strangers--I have too many favorites to quantify any as the best book ever. The man does need to stop writing until I have a chance to catch up on the last 40 years though. Sheesh!

If I'm making music now, it's only on the drums. I have no inclination to sing after a few hours of doing that. Maybe someday I will again though...

Lightning, False Memory, Odd Thomas, Phantoms, Strangers--I have too many favorites to quantify any as the best book ever. The man does need to stop writing until I have a chance to catch up on the last 40 years though. Sheesh!

If I'm making music now, it's only on the drums. I have no inclination to sing after a few hours of doing that. Maybe someday I will again though...

Would patterncount be better if a few of the colors where Yellow Green, and Grass Green? Where the word green is the second word in the name. Or would LeftWords be better?

Would patterncount be better if a few of the colors where Yellow Green, and Grass Green? Where the word green is the second word in the name. Or would LeftWords be better?

Would patterncount be better if a few of the colors where Yellow Green, and Grass Green? Where the word green is the second word in the name. Or would LeftWords be better?

If the first word always signifies the color, then LeftWords would be fine. If you have an instance such as Yellow Green, and Green is the color, then it will not work. If Yellow is the color, then it would. If you use a Case with PatternCount, you'll have an issue determining the hierarchy of colors. If you test for Green before Yellow, the result will be Green. If you test for Yellow before Green, the result will be Yellow.

It's seems that the most reliable structure would be to include signifying color before description or other color and then to use LeftWords. Yellow Green would then produce Yellow and Green Yellow would produce Green. Grass Green would have to be changed to Green Grass so that Grass isn't returned as a color. If this isn't an option for you, you could use a combination of LeftWords and PatternCount.

Let( C = LeftWords( colorField; 1 );

Case( Position( " red orange yellow green blue indigo violet "; " " & C & " "; C; PatternCount( " " & colorField & " "; " red " ); "red"; PatternCount( " " & colorField & " "; " orange " ); "orange"; ... ) )

Again, this wouldn't be foolproof unless combination colors such as Yellow Green were defined with the signifying color listed first. Grass Green would be fine since it only contains one color, which would be identified after the Position test portion of the Case failed and the 'green' test was successful.

If the first word always signifies the color, then LeftWords would be fine. If you have an instance such as Yellow Green, and Green is the color, then it will not work. If Yellow is the color, then it would. If you use a Case with PatternCount, you'll have an issue determining the hierarchy of colors. If you test for Green before Yellow, the result will be Green. If you test for Yellow before Green, the result will be Yellow.

It's seems that the most reliable structure would be to include signifying color before description or other color and then to use LeftWords. Yellow Green would then produce Yellow and Green Yellow would produce Green. Grass Green would have to be changed to Green Grass so that Grass isn't returned as a color. If this isn't an option for you, you could use a combination of LeftWords and PatternCount.

Let( C = LeftWords( colorField; 1 );

Case( Position( " red orange yellow green blue indigo violet "; " " & C & " "; C; PatternCount( " " & colorField & " "; " red " ); "red"; PatternCount( " " & colorField & " "; " orange " ); "orange"; ... ) )

Again, this wouldn't be foolproof unless combination colors such as Yellow Green were defined with the signifying color listed first. Grass Green would be fine since it only contains one color, which would be identified after the Position test portion of the Case failed and the 'green' test was successful.

If the first word always signifies the color, then LeftWords would be fine. If you have an instance such as Yellow Green, and Green is the color, then it will not work. If Yellow is the color, then it would. If you use a Case with PatternCount, you'll have an issue determining the hierarchy of colors. If you test for Green before Yellow, the result will be Green. If you test for Yellow before Green, the result will be Yellow.

It's seems that the most reliable structure would be to include signifying color before description or other color and then to use LeftWords. Yellow Green would then produce Yellow and Green Yellow would produce Green. Grass Green would have to be changed to Green Grass so that Grass isn't returned as a color. If this isn't an option for you, you could use a combination of LeftWords and PatternCount.

Let( C = LeftWords( colorField; 1 );

Case( Position( " red orange yellow green blue indigo violet "; " " & C & " "; C; PatternCount( " " & colorField & " "; " red " ); "red"; PatternCount( " " & colorField & " "; " orange " ); "orange"; ... ) )

Again, this wouldn't be foolproof unless combination colors such as Yellow Green were defined with the signifying color listed first. Grass Green would be fine since it only contains one color, which would be identified after the Position test portion of the Case failed and the 'green' test was successful.

Hi,

At the end, you'd better build a value list based on some other Table with a construction of type :

- serial

- full : Yellow Green

- color : Green

Hi,

At the end, you'd better build a value list based on some other Table with a construction of type :

- serial

- full : Yellow Green

- color : Green

Hi,

At the end, you'd better build a value list based on some other Table with a construction of type :

- serial

- full : Yellow Green

- color : Green