# utilizing same calculation for different fields?

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

## Recommended Posts

Hello,

I have a range of distances that need to be grouped eg case(n>=600, 1, n<600 and n>=500, 2, n <500 and n>=400, 3, etc through to -600). There are four seperate fields that require this process. And there are another three field groups in addition to distance that require similar processing.

At the moment I can only think of creating a case statement for each individual field, even though the calc would be more or less the same for each field group. In other words 16 case statements, each statement with 14 possibilities, even though there are in effect only four calcs being used.

Is there a smarter way? Because this will be a daily event involving a large number of records, I'm seeking a "hard-wired" solution rather than a scripted approach.

Thanks for any suggestions.

##### Share on other sites

You do not need to use ranges in your case statement. You could just do the following:

Case( n >= 600, 1,

n >= 500, 2,

n >= 400, 3,

....

n >= -500, 15,

16)

You can copy and paste the defination from one field into another.

Sorry, but that is the best I can do.

##### Share on other sites

Hi Glenn, i see you have FM6, but allow me to point out that in FM7 you can create custom functions:

GroupDist (Field ) = Case ( Field>=600 , 1 , Field>=500 , 2 , ... )

I'm sure the syntax is somewhat different, but you get the idea. I only point this out because if this is an important thing, you might think of upgrading.

Jerry

##### Share on other sites

Here's a way you can do it using ValueListItems and a repeating calculation. I don't have much time to explain right now, but if you like it, I'll describe more later when I return from Stomp. You only have to change one parameter for it to apply to another field, and an Update button is required for screen refresh.

GlennC.zip

##### Share on other sites

Yes, true repeating fields could be interresting in this process.

Actually, I don't get why you used a relationship here, as Min(cTestNums) would work as well, without the update trick.

I just played a bit with your approach, with another calc and an index result.

Not sure it is 100% reliable though, as I didn't use the TextToNum approach, nor did I extensively tested with sample data.

BTW JayTee, your calc won't work for distances longer than -600 and a distance of 0 would be grouped with 1...

This said, I miss Dj too

GlennC2.zip

##### Share on other sites

Another possibility would be math (yick!):

if ( n>= 600; 1; 7 - Div(n,100) )

Oops, for FM6 you could use:

if ( n >= 600; 1; 7 - Int( if ( n < 0, n-99, n ) / 100 ))

##### Share on other sites

Ok, plenty of ideas. Ralph - thanks, I didn't realize that. There's half the typing solved just in that.

Quintech, custom functions...very interesting. In my pondering I had wished for some kind of a conditional lookup creation: case(field>n, lookup(something) etc. I need to see past the work/learning of upgrading to the benefits that await.

Queue, Ugo, Shadow, thanks for the input and brain power. I'm absorbing it all now. The use of Choose and Extend - I would never have thought of it.

##### Share on other sites

You're correct. Like I said, I was in a hurry. The relationship isn't necessary. You could add a Case for zero, although I believe it should be in rank 7 from the pattern of the original query. And I didn't think anything smaller than -600 should produce a result, at least it doesn't appear that way from the question.

##### Share on other sites

In fact, it could be even possible to solely rely upon the ValueListItems trick, without involving any repetition, just playing with PatternCount as you did elsewhere...

This one is buggy at the end but the logic stays, though I believe it becomes more complicated than necessary, compared to readable Case Statements.

GlennC2.fp5.zip

##### Share on other sites

I figured something like this could be done, but I couldn't get the logic assembled with the little time I had last night. Quite impressive, though I believe more now than ever that you are indeed certifiable.

##### Share on other sites

Geez !

Here's the 2 fields + VL way....a complete brainfart

GlennC3.zip

##### Share on other sites

<bows down> I'll have to interpret this later. Your brain farts better than mine elucidates.

##### Share on other sites

-Queue- said:

... Quite impressive, though I believe more now than ever that you are indeed certifiable.

To all of you, I find this kind of braininess a little scary!

##### Share on other sites

Okay, the pain in my shoulder is making it too difficult for me to dissect your last attachment properly. Could you pretend I'm an idiot and break it down for me logically?

##### Share on other sites

Hi,

Well, reviewing it, I made it too complicated...

First, it may not exactly answer Glenn need, though I think it might be useful and adapted to convert a Numeric Value List into ranges of "regular cycles", and then build relationship (with prior version of FM) based on it, even with related value lists.

Glenn wants to classify values this way

>600 = 1

> 500 and <=600 =2

>400 and <=500 = 3

etc.

up to <-600

As you know, we can determine the position of a value in a list with a standard calculation of :

WordCount(Left(YourValueList, Position("

1086334992-GlennCorrected.fp5.zip

##### Share on other sites

Ugo,

Your solution has duplication of 8, both 0 to -100 and -100 to -200 are using 8.

Also, the original request stated: Case( distance >= 600, 1, distance >= 500, 2, ...

your solution gives 2 for 600, 3 for 500, etc.

See attachment for my alternative.

GlennAlt.zip

##### Share on other sites

Hi,

I was focused on the VLI trick, which is why I explained that it wasn't answering Glenn request.

Though, there is some flaw in my last attachment. I'd try to fix it.

But still...you're right about Maths you clever man.

##### Share on other sites

Nice job, Shadow. Although I believe it should stop at -600, I'm not sure what every rank should be below that, 13, 14?

##### Share on other sites

You can also cut it down to one If and use

If( distance1 > 600, 1, Int( (800 - distance1)/ 100) - not Mod( distance1, 100 ))

##### Share on other sites

Queue,

That's a neat change - I'll put that trick on my list.

Ugo,

Sorry, didn't mean to interrupt the interesting VLI discussion, I just wanted to make sure Glenn had what he needed before we completely hijacked his thread.

##### Share on other sites

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

## Create an account

Register a new account