calc inconsistent

[Cortical]

Hi All,

FM10A OSx.5.7

A calc evaluates an (wage) amount against a list of (tax threshold) values and should return the value list row (rate) number. input into g_amt:

100 returns 1

300 returns 1

301 returns 2

400 returns 2

500 returns 3

600 returns 3

900 returns 3

then it all goes wrong...

901 returns blank

1000 return 2

substituting A3, A4 etc for RATE, returns the correct values, that shows the case argument rows are evaluating the correct values, and the pattern looks correct and logical in the case statement; but a blind spot has developed... Illumination would be appreciated.

----------

The g_list of values:

300

500

900

1200

3000

100000

the calc:

Let([

INC = g_amt ;

VL = g_list ;

A1 = GetAsNumber( GetValue ( VL ; 1 )) ;

A2 = GetValue ( VL ; 2 ) ;

A3 = GetValue ( VL ; 3 ) ;

A4 = GetValue ( VL ; 4 ) ;

A5 = GetValue ( VL ; 5 ) ;

A6 = GetValue ( VL ; 6 ) ;

A7 = GetValue ( VL ; 7 ) ;

A8 = GetValue ( VL ; 8 ) ;

RATE= Case(

INC ≤ A1 ; 1;

A1 < INC and INC ≤ A2 ; 2;

A2 < INC and INC ≤ A3 ; 3;

A3 < INC and INC ≤ A4 ; 4;

A4 < INC and INC ≤ A5 ; 5;

A5 < INC and INC ≤ A6 ; 6;

)

];

RATE

)

test file attached if anyone cares to have a look.

test.fp7.zip

[mr_vodka]

This seems convoluted. I think that you should store these values into its own table.

[Cortical]

yes, they are actually stored in their own table, the global list was just a means of simplifying the problem.

It is really just a range problem: i.e. there is a list of (upper limit) thresholds; which member of the list does the global number fall below?

Edited June 11, 2009 by Guest

[comment]

The best way to do this, IMHO, is by a lookup (if no match, lookup next lower) or by a relationship using the > operator.

[Cortical]

I have moved away from lookups generally but it would be an option.

A sorted rel based on g_amt < X also works.

THe fix has been to define the first block using GetAsNumber

Let([

INC = g_amt ;

VL = g_list ;

A1 = GetAsNumber( GetValue ( VL ; 1 )) ;

A2 = GetAsNumber( GetValue ( VL ; 2 )) ;

A3 = GetAsNumber( GetValue ( VL ; 3 )) ;

A4 = GetAsNumber( GetValue ( VL ; 4 )) ;

A5 = GetAsNumber( GetValue ( VL ; 5 )) ;

A6 = GetAsNumber( GetValue ( VL ; 6 )) ;

A7 = GetAsNumber( GetValue ( VL ; 7 )) ;

A8 = GetAsNumber( GetValue ( VL ; 8 )) ;

RATE= Case(

INC ≤ A1 ; 1;

A1 < INC and INC ≤ A2 ; 2;

A2 < INC and INC ≤ A3 ; 3;

A3 < INC and INC ≤ A4 ; 4;

A4 < INC and INC ≤ A5 ; 5;

A5 < INC and INC ≤ A6 ; 6;

)

];

RATE

)

So I now have (3) working approaches.

What remains unexplained is why the first 3 arguments worked, and subsequent did not. Inconsistency.

[comment]

It's consistent with alphabetical sorting.

[Cortical]

For the record.

Dave (d-42) from comp.databases.filemaker supplied this explanation

<<

it is perfectly consistent why the first three

worked, but I'll expand on exactly why.

As text the comparison is simply left to right. So "4360" < "4370" and

"5000" > "437" but "1000" < "2"

for "200": it matched the case that "200" < "259", (because first char

2<=2, second char 0 <= 5)

for "300", it matched the case that "259" < "300" (because first char

2 <= 3), and "300" <= "356" (first char 3=3, and second char 0 <= 5)

for "836", it matched the case that "300" < "836" because first char 3

< 8), and "836" <= "836" (the two strings are equal)

for "837", it didn't match any case.

837 is not <= 259 because 8 > 2

837 is not <= 356 beacuse 8 > 3

837 is not <= 836 because 8=8, 3=3, but 7>6

837 is not <= 1221 because 8>1

837 is not <= 3144 because 8>3

837 is not <= 100000 because 8>1

>>>

[LaRetta]

That's exactly what alphabetical sorting means in this instance, as explained by Comment. :wink2: