I'm looking for a technique that will take a report similar to this:

DIV REG ACADEMY REGISTRANTS
01 RKNX ABC 100
01 RKNX DEF 50
01 RKNX GHI 75
01 RAZ1 RST 100
01 RAZ1 UVW 200
01 RAZ1 XYZ 50

and show the ranking of the Academies Registrants within their respective Regions, ie.

DIV REG ACADEMY REGISTRANTS RANK
01 RKNX ABC 100 1
01 RKNX GHI 75 2
01 RKNX DEF 50 3
01 RAZ1 UVW 200 1
01 RAZ1 RST 100 2
01 RAZ1 XYZ 50 3

Not having much luck with the RANKING function.
Is this possible?

Thanks,
Chuck Mason

Is this what you're trying to do:

TABLE FILE CAR
SUM
COMPUTE RANK/I1 = IF COUNTRY EQ LAST COUNTRY THEN RANK + 1 ELSE 1;
BY COUNTRY
BY CAR
BY HIGHEST RETAIL_COST
END

looks like you've got your registrants ranked within their regions; so i dont' think we're clear on what you need; at least i'm not; show us your fex; and its always best to replicate your problem using the CAR file so we can take your actual fex , run it, and tweak it.

quote:

Originally posted by reFOCUSing:
Is this what you're trying to do:

TABLE FILE CAR
SUM
COMPUTE RANK/I1 = IF COUNTRY EQ LAST COUNTRY THEN RANK + 1 ELSE 1;
BY COUNTRY
BY CAR
BY HIGHEST RETAIL_COST
END

Guru -

This is what I had seen and tried, but what I want is the ranking of the Cars' RETAIL_COST within the Country. So instead of the Jaguar that costs $8878 being ranked 2nd, I want it ranked 3rd overall for England with the Jensen moving up to 1st and the other Jaguar($13491) moving down to 2nd. This is where I am failing.

Thanks for the help!
Chuck Mason

quote:

Originally posted by susannah:
looks like you've got your registrants ranked within their regions; so i dont' think we're clear on what you need; at least i'm not; show us your fex; and its always best to replicate your problem using the CAR file so we can take your actual fex , run it, and tweak it.

Susannah -
The second example is the way I would like the report ordered, with the Academies ordered by their Registrants within each Region...unlike the first report where the academies are sorted with their BY fields; Division, Region, then Academy.
Thanks
Chuck

Would this work for you?:

TABLE FILE CAR
SUM
COMPUTE RANK/I1 = IF COUNTRY EQ LAST COUNTRY THEN RANK + 1 ELSE 1;
BY COUNTRY
BY HIGHEST RETAIL_COST
BY CAR
ON TABLE SET STYLE *
TYPE=REPORT, COLUMN=CAR,SEQUENCE=2,$
END

Rick -

Thanks! That works too! I had gotten Guru's code to work by changing the CAR field from a BY to SUM field.

What I was really hoping to get from the post was that, once I solved the ranking issue, I am looking for a method to grab a particular value for 'Retail Cost' to utilize in another application (using the CAR file as an analogy). Specifically, I would like to find the top 1/3 ranked CAR by COUNTRY and utilize the RETAIL COST for that particular CAR in another application. For example, let's say there was 15 cars in a Country on the report, and they were ranked(HIGHEST) by RETAIL COST. What I am trying to gather is the value of RETAIL COST for CAR # 5 (# CARS in REGION/3) within THAT region. I want to utilize that value as a TARGET value in our IBI PMF application for the Measure Retail Cost for each Country.
So this code would compute a Target value for each Country, based upon the ranking of the CARs within the Country.
I probably am not explaining this well, but if you have any techniques for this it would be greatly appreciated!
Thanks!
Chuck Mason

BY HIGHEST 5 RETAIL_COST
is syntax that will give you a short list of only the top 5...
or...
BY COUNTRY
BY HIGHEST RETAIL_COST
BY CAR
ON TABLE HOLD
END
then
SUM FST.RETAIL_COST BY COUNTRY
ON TABLE HOLD
END
...now you have a file of 1 record per country with the highest (first) retail cost from your extract file;
..if you want to use these values, 1 per country, in your next fex, then make a decode file.
SUM COMPUTE BLANK/A1=' '; FST.RETAIL_COST BY COUNTRY
ON TABLE HOLD AS MYLIST FORMAT ALPHA
END
...then in your next fex
DEFINE FILE whatever
MYCOST/Dn=DECODE COUNTRY(MYLIST);
END
...or if you want only 1 value out of that whole first deal, then just save a 1 line record, and -READ the variable into an &VAR.
I'll keep going if i'm anywhere near what you want...

Chuck,

If the REGISTRANTS value is already summed then just add the field you want your report sorted by at the necessary point but add NOPRINT -

TABLE FILE filename
SUM REGISTRANTS
    COMPUTE RANK/I3 = IF DIV EQ LAST DIV
                     AND REG EQ LAST REG
                     AND ACADEMY EQ LAST ACADEMY
                     THEN LAST RANK + 1 ELSE 1;
 BY DIV
 BY REG
 BY HIGHEST REGISTRANTS NOPRINT
 BY ACADEMY
END

If REGISTRANTS are not already summed then you could parse the data to achieve the summation and HOLD it first.

To achieve your ultimate goal then try this sample -

TABLE FILE GGSALES
COUNT UNITS AS UNIT_COUNT
BY REGION
BY ST
PRINT UNITS
      COMPUTE RANK/I3 = IF REGION EQ LAST REGION AND ST EQ LAST ST
                       AND CITY EQ LAST CITY THEN LAST RANK + 1 ELSE 1;
      COMPUTE THIS_IS_IT/A1 MISSING ON = IF RANK EQ CNT.UNITS / 3
                       THEN 'Y' ELSE MISSING;
BY REGION
BY ST
BY HIGHEST UNITS
BY CITY
END

T

Susannah -

Thanks for the post, but it's not quite there. What I have is a ranked list of the HIGHEST RETAIL_COST of CARS BY COUNTRY (actually my data is REGION and SCHOOLS and %ENROLLMENT Indexed to prior year). Now with that list, I want to take the number of CARS in each Country, divide by 3, then get the RETAIL_COST value of that Ranked CAR. For example, say there are 15 CARS in one COUNTRY, that are ranked 1-15 by RETAIL_COST. I want to take 15/3=5...the 5th ranked CAR in that COUNTRY, grab THAT RETAIL_COST value, then add a computed field for those CARS in that COUNTRY = to that RETAIL_COST. This needs to be repeated for each COUNTRY.
I'm not sure if I'm explaining this well...but I am at a loss to try and figure this one out.

Thanks!
Chuck Mason