Max 2

[jawadfrhad]

* your code here


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

 

[Olaf Doschke]

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

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:

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:

* 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

 

[Olaf Doschke]

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

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

Bye, Olaf.


Olaf Doschke Software Engineering

https://www.doschke.name

 

[mjcmkrsr]

Hi,

... or you could try something like this

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

 

[mm0000]

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

 

[Mike Lewis]

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

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

 

[Mike Lewis]

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:

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

 

[Mike Lewis]

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