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.

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.

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

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

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

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

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.

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.

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

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.

Geez !

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

GlennC3.zip

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

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

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?

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

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

Hi,

Yes I saw your previous calc, and already tried it succesfully.

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.

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

You can also cut it down to one If and use

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

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.