Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Filter in Value 4

Status
Not open for further replies.

abbasaif

ISP
Oct 8, 2018
89
AE
Hi

How can I filter the amount between 0.01 to 0.99 and -0.10 to -0.99.

Thanks

Abbasaif
 
Hi,

It should be less than 1.00 and greater than -0.99.


Thanks

Abbasaif
 
I don't know what you tried and why you failed, but I make an assumption about an observation I made very often. Wrong usage of AND and OR boolena operators by thinking too much in natural language conjunctions, which usually are used fuzzy and with ill logic.

To have rangee1 AND range1 as valid ranges you test for them with boolean OR operation:

Code:
SET FILTER TO BETWEEN(value,min1,max1) OR BETWEEN(value,min2,max2)

You say you want the one AND the other range to be valid, but that means in boolean algebra the one OR the other range is valid because unless the ranges overlap, a value cannot be in one range AND the other range at the same time. And even if they overlap, the boolean AND conjunction will only make the overlap range a valid range, not the extended range. Very generally keep in mind AND is a reducational operation, it's not adding and summing of values fulfilling any of the single conditions. AND adds something, it adds a condition. And that makes it more restrictive, a value that fulfills with multiple conditions is more and more restricted to a smaller set, the result set is subtracted from. In contrast when you OR several conditions it's sufficient to fulfill any one of them, so that adds to the result set.

And then there are brackets pulling together single simple conditions to more complex ones and you can have multiple levels of AND and OR conjunctions. It's complicated and just don't do it in your head, do it on examples that give you confidence about having the overall condition you want. Put away your feeling for natural language and turn on your knowledge about boolean algebra or look it up, when you design your filter conditions or where clauses.

Bye, Olaf.

Olaf Doschke Software Engineering
 
Hi,

In addition to Olaf's post, please have a look at this excerpt from Hacker's Guide to Visual Foxpro 7 by Tamar A. Granor, Ted Roche et al.

Operator, Operator, Give Me Number Nine

"Reeling and Writhing, of course, to begin with," the Mock Turtle replied, "and the different branches of Arithmetic—Ambition, Distraction, Uglification, and Derision."

Lewis Carroll, Alice's Adventures in Wonderland, 1865

Remember seventh grade (or maybe it was sixth or eighth) when you learned a mnemonic that went something like "Please Excuse My Dear Aunt Sally?" Right about now, you're probably scratching your head and saying, "Oh, sure, those are the Great Lakes, right?," or "Yeah, that sounds familiar, but what's it about?" unless, like us, you remember this kind of stuff forever, and you're sitting there saying "Sure, parentheses, exponents, multiplication, division, addition, subtraction." You got it: The mnemonic provides the order of precedence of numeric operators. That is, when you see:

3 + 7 * 4
you know that it's 31, not 40, because multiplication comes before addition (except in the dictionary). On the other hand:

(3 + 7) * 4
is 40 because parentheses come before any other operators. When there are multiple operators at the same level, they're evaluated from left to right.

In Xbase, there are a bunch of other operators besides arithmetic operators. Comparison operators (=, <>, <, >, <=, >=, and $) are used to compare values (big surprise). Logical operators (AND, OR, NOT) are used to combine logical expressions (often comparisons).

Some of the operators used for arithmetic can also be applied to other data types (all of them for double, integer and currency, + and - for characters, - for dates and datetimes). There's also one additional arithmetic operator (%, which is the same as MOD()) that applies to numeric, float, double, integer and currency values.

The arithmetic precedence rules have been extended to cover the full set of operators. Arithmetic operators used on other types have the same precedence as when they're used with numbers. The % has the same precedence as multiplication and division.

The complete precedence rules for FoxPro are somewhat different from those in some other programming languages. In particular, logical operators come below arithmetic and comparison operators:

Parentheses

Exponentiation

Multiplication/Division/Modulo

Addition/Subtraction

Comparison

NOT

AND

OR

Maybe the FoxPro version of the old mnemonic should be "Please Excuse Miss Daisy Mae And Sally Combing Nits All October?" Maybe not.

hth

MarK
 
Hi,

Thanks for the reply!

I tried this and got it

Code:
Select agreport
Set Order To cref1
Do Case
   Case This.Parent.chkValue.Value = 1
      SET FILTER TO !(totday < 1.00 AND totday > -0.99)
   Otherwise
      Set Filter To
Endcase
Go Top

Note: I want the value which should be greater than 1.00 and should not less than -0.99.

Abbasaif
 
Abbasaif,

If you want the result to be greater than 1.00 You could apply:
Code:
set filter to > 1.00
this will filter the records with an value of less than 1.00
if you want the result not to be less than -0.99
Code:
set filter to > -0.99
this will filter out all re ords with a value of -0.98 and less

Regards,
Koen

 
I want the value which should be greater than 1.00 and should not less than -0.99.

Are you sure about that? If it is greater than 1.00, it must be not less that -0.99.

Earlier, you said that you wanted it to be "less than 1.00 and greater than -0.99". In other words, between -0.99 and 1.00. If that's what you want, my original suggestion would be the simplest, and would work:

Code:
SET FILTER TO BETWEEN(Amount, -0.99, 1)

If that's not right, perhaps you could clarify the requirement.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads
 
Sorry Mike,

Actually I don't want the value less than 1.00 and not greater than -0.99.

Regards

Abbasaif
 
Abbasaif,

So you want values > 1.00 and > -0.99 ?
try: ? between(nValue,1.00,-0.99)
Koen
 
Hi Koen,

nValue = .5

? between(nValue, 1.00, -0.99)

... yields .F. whereas

Code:
 ? between(nValue, -0.99 , 1.00)

as Mike suggested, yields .T. Please check the syntax of the function

BETWEEN(eTestValue, eLowValue, eHighValue)

hth

MarK
 
I see this threads latest posts as a classical case of not able to really describe your need.

Let me take it in reverse. Assuming this condition is working correctly:
Code:
!(totday < 1.00 AND totday > -0.99)

Then what this does is exclude the range from >-0.99 to <1.00, just please order your conditions so it becomes more obvious:
Code:
!(totday > -0.99 AND totday < 1.00)

What you said initially, though, would require two ranges, as a range nearer to 0 would then still be allowed.

I also will guess you actually want to exclude another range to be more precise, >-0.99 sounds more like you wanted to say >=-1 or >-1.

Now, the way to see whether this really is what you need is instead of filtering the table data with it and browsing results and judging them as OK, fits. Just test with extreme cases you know you want to be in or out. SELECT 0, aka having no table open in the currency workarea, then set a variable totday to extreme values like -1, -0.99, -0.97, 0.99 1.0 and and then check whether the above expression gives you the expected .T. or .F. values for respective values of the totday veriable.

It's really not hard to test what you want. Because you can simply make up the cases that would test the limits, even if they don't exist in your data. Another obvious way would be to add the records with the extreme cases.

Bye, Olaf.

Olaf Doschke Software Engineering
 
And another detail: When you browse data and assume what you see is what you have. A value displayed as -0.99 might also be -0.9900001 or anything diverting a little from the value displayed, as binary storage of numeric values always differs from decimal values displayed in non-integers.

So again, I'd rather make all conditions about the real essential limits of -1 and 1 and pick the comparison operators to include or exclude these value by picking from <,>,<=, or >= instead of adjusting values of the condition from 1 to 0.99, this is always the worse way of handling the corner cases.

Bye, Olaf.

Olaf Doschke Software Engineering
 
Mark,
you are of course very correct.

Mike,
sorry I overlooked your correct answer in this long thread, as a matter of fact the first and only correct one. Again sorry.

Regards,
Koen
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top