algorithm needed

I have 6 numeric fields which contain numbers that are positive, negative, or zero.

I need to find the minimum absolute value that is not equal to zero for the following tests:
test_1: (field_A - field_B)
test_2: (field_C - field_B)
test_3: (field_D - field_E)
test_4: (field_F - field_E)

So if the value of the tests for one record are:
test_1: 5
test_2: 0
test_3: 456
test_4: -30

then :
test_1: ABS (field_A - field_B) = 5
test_2: ABS (field_C - field_B) = 0
test_3: ABS (field_D - field_E) = 456
test_4: ABS (field_F - field_E) = 30

The correct result would be 5, since it is the minimum of the absolute values that are not equal to zero.

Any ideas on how to code this? I seem to be stuck going in the wrong direction.

Thank you.

WF 7.7.03, Windows 7, HTML, Excel, PDF

Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006

Francis Mariani

Expert

posted

Hide Post

Here's one part of the answer: use the MIN function.

An example:

TABLE FILE CAR
SUM
COMPUTE TEST_1/D10 = MIN(ABS(WIDTH), ABS(HEIGHT), ABS(LENGTH));
BY COUNTRY
END

To exclude the zero values, I would evaluate them to an unnaturally high number, so that they would never be returned by the MIN function. The only hole in this suggestion is if all the tests return as zero.

DEFINE FILE CAR
TEST1/D10 = IF WIDTH - HEIGHT EQ 0 THEN 999999999 ELSE WIDTH - HEIGHT;
TEST2/D10 = IF HEIGHT - SEATS EQ 0 THEN 999999999 ELSE HEIGHT - SEATS;
TEST3/D10 = IF WIDTH - LENGTH EQ 0 THEN 999999999 ELSE WIDTH - LENGTH;
END
TABLE FILE CAR
SUM
TEST1
TEST2
TEST3
COMPUTE TEST_A/D10 = MIN(ABS(TEST1), ABS(TEST2), ABS(TEST3));
BY COUNTRY
END

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

JohnB

Platinum Member

posted

Hide Post

I was trying to avoid hardcoding the 999999999 (I have this aversion to hardcoding values), but it works better than not using it.

The all-zeros hole does need to be handled, but that's the easy part.

Thank you.

WF 7.7.03, Windows 7, HTML, Excel, PDF

Posts: 225 | Location: San Francisco Bay Area, California | Registered: October 26, 2006

Ignored post by JohnB posted

Show Post

Francis Mariani

Expert

posted

Hide Post

I think this does it:

DEFINE FILE CAR
TEST1/D10 = IF WIDTH - HEIGHT EQ 0 THEN 999999999 ELSE WIDTH - HEIGHT;
TEST2/D10 = IF HEIGHT - SEATS EQ 0 THEN 999999999 ELSE HEIGHT - SEATS;
TEST3/D10 = IF WIDTH - LENGTH EQ 0 THEN 999999999 ELSE WIDTH - LENGTH;
END
TABLE FILE CAR
SUM
TEST1
TEST2
TEST3
COMPUTE TEST_A/D10 = 
  IF MIN(ABS(TEST1), ABS(TEST2), ABS(TEST3)) EQ 999999999 THEN 0 ELSE MIN(ABS(TEST1), ABS(TEST2), ABS(TEST3));
BY COUNTRY
END

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Ignored post by Francis Mariani posted

Show Post

Leah

Virtuoso

posted

Hide Post

How hard would it be to eliminate the zero values at the start?

Leah

Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004

Ignored post by Leah posted

Show Post