×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Max

Max

(OP)

* your code here


hi all
need to find max value among fields AND PRINT FIELD NAME
same

RE: Max

In what form do you have data to start with? f1-f5 and empty columns highest and highest_name? Or do you have the raw data that was transformed to f1-f5 by the pivoting wizard?

Because if you have the data for f1-f5 in the form of rows instead of columns, you can use GROUP BY And MAX, now you can't. The lessen in that case woul be: Don't pivot first or aggegate while you pivot, not do this as an aftermath.

And if this is the data you start with in the first place, the lesson would be to not store it normalized. Same data always goes in rows, never in multiple column. It's a no go.

To get unpivoted data you could

CODE

SELECT recno() as row, "f1" as column  f1 as f FROM yourtable
UNION
SELECT recno() as row, "f2" as column  f2 as f FROM yourtable
...
INTO CURSOR UnionedData 

To have a table with columns in the structure (row, column, f)

Then you can do this:

CODE

Select hi.row, hi.highest, u2.column as highest_name from ;
(SELECT row, Max(f) as highest;
From UnionedData u1;
GROUP BY row) hi;
Left Join UnionedData u2 on u2.row=hi.row ;
having u2.f=hi.highest 

Nevertheless, you have some xbase ways on hand, too. If you already have the columns, do a two step process:

CODE

* step 1: populate highest:
replace all highest with max(f1,f2,f3,f4,f5)
* step 2: determine highest_name
replace all highest_name with "f5" for f5=highest
replace all highest_name with "f4" for f4=highest
replace all highest_name with "f3" for f3=highest
replace all highest_name with "f2" for f2=highest
replace all highest_name with "f1" for f1=highest 

Bye, Olaf.


Olaf Doschke Software Engineering
https://www.doschke.name

RE: Max

Just by the way, I have not forgotten icase, you can do this, but it gets unwieldy the more f columns you have:

CODE

replace all highest_name with icase(f1=highest,"f1",...,f4=highest,"f4","f5") 

Bye, Olaf.

Olaf Doschke Software Engineering
https://www.doschke.name

RE: Max

Hi,

... or you could try something like this

CODE -->

LOCAL lni, lnValue

CREATE CURSOR csrResults (iResults I, cName C(5))
CREATE CURSOR curTemp (f1 I, f2 I, f3 I)

FOR lni = 1 TO 25
	INSERT INTO curTemp VALUES ( Int(Rand() * 25) + 1, Int(rand() * 25), Int(Rand() * 25) + 2)

ENDFOR

LOCATE 
BROWSE NOWAIT 

SCAN
lnValue = 0	
lcName  = ""
	FOR lni = 1 TO FCOUNT()
		IF EVALUATE(FIELD(lni)) > lnValue
			lnValue = EVALUATE(FIELD(lni))
			lcName = FIELD(lni)
		ENDIF 
		
		IF lni = FCOUNT()
			INSERT INTO csrResults VALUES (lnValue, lcName)

		ENDIF 
	ENDFOR 
ENDSCAN 

SELECT csrResults
BROWSE
CLOSE ALL 

hth

MarcK

RE: Max

CODE --> Code

USE table
replace all highest with max(f1,f2,f3,f4,f5)
locate
scan while not eof()
   for int = 1 to 5
       mfield = 'f'+allt(str(int))
       if &mfield = highest
          replace highest_name with mfield
          exit 
       endif
   endfor
endscan 
USE

RETURN 

RE: Max

I see you have had some good replies. Here is my contribution, for what it's worth:

CODE -->

SELECT TheTable
REPLACE ALL Highest WITH MAX(F1, F2, F3, F4, F5)
REPLACE ALL Highest_Name WITH ;
  ICASE(  ;
    F1 = Highest, "F1", ;
    F2 = Highest, "F2", ;
    F3 = Highest, "F3", ;
    F4 = Highest, "F4", ;
    F5 = Highest, "F5" ) 

I'm assuming that there always exact five fields to be tested, and that these are always named F1, F2, etc.

Edit: Ooops. I just noticed that Olaf had posted a similar solution earlier in the thread. Sorry.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Max

Quote (Me)

I'm assuming that there always exact five fields to be tested, and that these are always named F1, F2, etc.

But if that's not the case (in particular, if you don't know the names of the fields in advance), this might work instead:

CODE -->

SELECT TheTable
LOCAL lcF1, lcF2, lcF3, lcF4, lcF5
lcF1 = FIELD(1)
lcF2 = FIELD(2)
lcF3 = FIELD(3)
lcF4 = FIELD(4)
lcF5 = FIELD(5)

REPLACE ALL Highest WITH ;
  MAX(EVALUATE(lcF1), EVALUATE(lcF2), EVALUATE(lcF3), EVALUATE(lcF4), EVALUATE(lcF5))
   
REPLACE ALL Highest_Name WITH ;
  ICASE(  ;
    EVALUATE(lcF1) = Highest, lcF1, ;
    EVALUATE(lcF2) = Highest, lcF2, ;
    EVALUATE(lcF3) = Highest, lcF3, ;
    EVALUATE(lcF4) = Highest, lcF4, ;
    EVALUATE(lcF5) = Highest, lcF5 ) 

(This is NOT tested.)

If you don't know how many fields that are to be tested, or if you don't know that they are the first fields in the structure, then I think you will be out of luck.

Edit: I just noticed a mistake in the first REPLACE. I have now corrected it. The above code should now work OK.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: Max

Jawadfrhad,

You've now had several replies to your question. It would be helpful if you could let us know if you found any of these useful, and whether you have now solved the problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close