Jump to content

utilizing same calculation for different fields?


GlennC
 Share

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.

Link to comment
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.

Link to comment
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

Link to comment
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. grin.gif 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

Link to comment
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 grin.gif

GlennC2.zip

Link to comment
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.

Link to comment
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.

Link to comment
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

Link to comment
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. wink.gif

Link to comment
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? banghead.gif

Link to comment
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

Link to comment
Share on other sites

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.

wink.gif

Link to comment
Share on other sites

This topic is 6436 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
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.