Hi,

I have a report something like this

name amount1 amount2 amount3

I want to get the top 10 names.

So, I have to sort these three columns and then i can get the top 10 names. I am not sure how to sort 3 columns.

Please suggest me a solution.\

Thank you,
Priya

This message has been edited. Last edited by: Kerry,

Top 10 names based on what?

You mean the names for the top 10 amounts.
You have 3 amounts, so you want the top 10 amounts from the 3 columns?

yes ,

i want the top 10 amounts from each column

Venkat - Thank you so much for the code...
I want the amounts also to be sorted highest. If I modify u r code by adding 'BY HIGHEST' to amount1,2,3 I see that only amount1,amout2 are sorted but i want all the columns to be sorted....

This message has been edited. Last edited by: Haripriya,

You can get the top 10 names based on one of the amount columns, but not all three - unless you want to base your sort/selection on the total of the three amount columns for each name.

Like Dan mentioned, you can sort one of the three amount columns but not all three.

If you want top 10 amounts from the column, you can do BY HIGHEST 10 fieldname

Here is the sample code:
SET ASNAMES = ON
TABLE FILE CAR
SUM
COUNTRY
BY DEALER_COST AS 'AMOUNT1'
BY RETAIL_COST AS 'AMOUNT2'
BY SALES AS 'AMOUNT3'
BY CAR AS 'NAME'
ON TABLE NOTOTAL
ON TABLE HOLD AS HLD_CAR
END

TABLE FILE HLD_CAR
PRINT
NAME
BY HIGHEST 10 AMOUNT1
BY HIGHEST AMOUNT2
BY HIGHEST AMOUNT3
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END

This message has been edited. Last edited by: Venkat-,

Clear requirements and examples always help in giving an answer.

From what I get from your edited post Haripriya is that when you look at the entire columns for Amount1, Amount2, and Amount3 they are not all sorted.

In Venkat's example all of the Amount fields are sorted. Sure if you look at the entire report the Amount2 and Amount3 fields may not be sorted but they are sorted within the previous Amount field.

If you want to treat all three Amount fields as the same and then sort them you need it all in one field. The best way to do this without adding together all of the Amounts is to copy each line for each Amount field. To do that you need to use the Macgyver technique.

Here is an example that will tell you which Amount field the value came from

SET ASNAMES = ON
TABLE FILE GGSALES
PRINT
COMPUTE NAME/A255=PCD || ' ' | STCD ;
UNITS AS 'AMOUNT1'
COMPUTE NEW_DOLLARS/I7=DOLLARS/10; AS 'AMOUNT2'
BUDUNITS AS 'AMOUNT3'
ON TABLE HOLD AS HOLD_AMTS
END

FILEDEF MCGMAS DISK MACGYVER.MAS
FILEDEF MCGDAT DISK MACGYVER.DAT
-RUN
-WRITE MCGMAS FILE=MACGYVER,SUFFIX=FOC
-WRITE MCGMAS SEGNAME=MAC1,SEGTYPE=S1
-WRITE MCGMAS   FIELD=BLANK  ,     ,A1,INDEX=I,$
-WRITE MCGMAS SEGNAME=MAC2,SEGTYPE=S1,PARENT=MAC1
-WRITE MCGMAS   FIELD=COUNTER,ORDER,I4,$
-RUN

 CREATE FILE MACGYVER
 MODIFY FILE MACGYVER
 COMPUTE CTR/I9=;
 FIXFORM 3(CTR/4 X-4)
 COMPUTE
   BLANK=' ';
   COUNTER=IF COUNTER EQ 0 THEN CTR ELSE COUNTER+1;
 MATCH BLANK
    ON MATCH CONTINUE
    ON NOMATCH INCLUDE
 MATCH COUNTER
    ON MATCH CONTINUE
    ON NOMATCH INCLUDE
 DATA
1
 END
-RUN

JOIN
BLANK WITH NAME IN HOLD_AMTS TO
BLANK IN MACGYVER AS B_
END

DEFINE FILE HOLD_AMTS
BLANK/A1 WITH NAME=' ';

AMOUNT_VALUE/I7=IF COUNTER EQ 1 THEN AMOUNT1
ELSE IF COUNTER EQ 2 THEN AMOUNT2
ELSE IF COUNTER EQ 3 THEN AMOUNT3
ELSE 0;

AMOUNT_FROM_FIELD/A10=IF COUNTER EQ 1 THEN 'AMOUNT1'
ELSE IF COUNTER EQ 2 THEN 'AMOUNT2'
ELSE IF COUNTER EQ 3 THEN 'AMOUNT3'
ELSE '';
END

TABLE FILE HOLD_AMTS
PRINT 
NAME
AMOUNT_FROM_FIELD
AMOUNT1
AMOUNT2
AMOUNT3
BY HIGHEST 10 AMOUNT_VALUE
END

Perhaps, as Crymsyn suggests, you want the top names based on the highest amount in any of the amount columns? If so, here's another way to do that:

SET ASNAMES  = ON
SET HOLDLIST = PRINTONLY
-*
TABLE FILE CAR
 SUM
  DEALER_COST/D8  AS 'AMOUNT1'
  RETAIL_COST/D8  AS 'AMOUNT2'
  SALES/D8        AS 'AMOUNT3'
 BY CAR           AS 'NAME'
 ON TABLE HOLD AS DATAFILE
END
-*
DEFINE FILE DATAFILE
 AMOUNT_TOP/D8 = MAX(AMOUNT1,AMOUNT2,AMOUNT3);
END
-*
TABLE FILE DATAFILE
 PRINT
  NAME
  AMOUNT1
  AMOUNT2
  AMOUNT3
  AMOUNT_TOP
 BY HIGHEST 10 AMOUNT_TOP NOPRINT
END

Dan,

You had the same idea as I had. This can be done in one pass:

  
-* File Priya02.fex
TABLE FILE CAR
SUM SALES RCOST DCOST
COMPUTE MAXVAL/D6=MAX(SALES, RCOST, DCOST);
BY TOTAL HIGHEST 5 MAXVAL
BY CAR
END

and add NOPRINT wherever desired.

Pfiou ...
The question seemed rather Low Level, but you all, great Focusians, made it a rather great exchange.
To multiply a dimension of a file (Crymsin approach) a simple Idea is to

AND  COMPUTE                      
     DIMGEO/I02 = 3 ;             
AND  COMPUTE                      
     DETGEO_GLOB   /A03 = '123' ; 

And overhead those Fields by describing them in the Master

 
FIELDNAME   =DIMGEO,        ,I02  , I04,$                            
$                                                                    
$  PSEUDO FILS DE NIVEAU MAT (POSSIBLE SUR FIX UNIQUEMENT)           
$                                                                    
SEGNAME=PSEUMAT,PARENT=SEDG111A,OCCURS=DIMGEO,$                      
FIELD=DETGEO      ,       ,A01       ,A01  ,$  NIVEAU AGREG DEMANDE  
FIELD=INDGEO      ,ORDER  ,I01       ,I04  ,$  RESTITUE L'INDICE MAT  

So, you have (at the PSEUMAT level ) 3 times the Data of the Father Segment. (Define must use WITH DETGEO to bring Father Data at PseuMat level)
This pre-historical technique led to Multi-Dimensional approach (Defining the hierarchy of a dimenson, Within etc ...)
From the past, with love
Focusely

Hi All,

I got the correct requirements now...they wanted to sort on the total comun(amount1+amount2+amount3) which is very meaningful....so it soves the problem....Thank u all for the responses...

Priya