How to Pass the parameters for geting report output as HIGHEST Or LOWEST 5(five) Records

First PARAMETER AS HIGHEST OR LOWEST
Second PARAMETER AS NUMBER example 5

where to make changes in code
if any example help me

An example:

-* FOCUS code:
-* File ranks.fex
-DEFAULT &HIGHLOW = 'HIGHEST';
-DEFAULT &NUMRANK = 2;
TABLE FILE CAR
SUM SALES
BY COUNTRY
BY &HIGHLOW &NUMRANK TOTAL SALES
BY MODEL
END

HTML form to invoke the ranks procedure:

< !--input type="hidden" name="IBIAPP_app" value="tests"/-->


HIGHEST
LOWEST

This message has been edited. Last edited by: <Mabel>,

Look at the previous posting to Pranali (Dynamic Ranking),
This gives you an example that auto prompt's (providing it's turned on) of how to do it.

Please pay special attention to the need to style before manually changing the code.

Speaking of RANKing, I have a tangential-related question. When you have identical sort field values, the RANK is the same, as expected. But when the sort field value changes, the RANK only increments by 1 instead of the actual positional value.

For example:

RANK Value Expected RANK
---- ----- -------------
1 99 1
2 98 2
3 96 3
3 96 3
4 91 5
5 89 6

That 5th line should have a RANK of 5, not 4, because there are 4 values ranked higher. And so on down the list.

Is there an option or alternative to achieve this?

I've encountered that same problem... you end up on occasion with 14 items on what you thought was your Top 10 list.

The way I got around it is adding in an additional field called RANK.

e.g


-* Maxlimit can be anything you want it to be
-* and can be passed as a parameter ...
-SET &MAXLIMIT = 10;
TABLE FILE CAR
PRINT COMPUTE RANK/I3 = RANK + 1;
SORTFIELD
FIELD1
FIELD2
BY HIGHEST SORTFIELD NOPRINT
WHERE TOTAL RANK LE &MAXLIMIT
END
So you get the same table format (columns in the same place) with a ranking system that increments every line.

Leo

This message has been edited. Last edited by: <Mabel>,

This looks more like a LIST than a RANK, and does not provide for ties. Unless I am interpreting it incorrectly

Sorry, I misread the post and just gave you the listing. For the Ranking purpose, the code I have is:




-* Extract the information you want to Rank
TABLE FILE TNAME
PRINT FIELD1 FIELD2
BY HIGHEST 10 SORTFIELD
ON TABLE HOLD
END

-* Define the proper ranking
DEFINE FILE HOLD
TEMP1/I3 = IF SORTFIELD EQ LAST SORTFIELD THEN 0 ELSE 1;
TEMP2/I3 = IF TEMP1 EQ 0 THEN LAST TEMP2 + 1 ELSE 1;
TEMP3/I3 = IF TEMP1 EQ 1 THEN TEMP3 + LAST TEMP2 ELSE LAST TEMP3;
RANK/I3 = TEMP3 + 1;
END

-* Display the information as you want.
-* whether you want the ranking to display a value
-*for each row or only when the value changes

TABLE FILE HOLD
PRINT RANK SORTFIELD FIELD1 FIELD2
BY HIGHEST SORTFIELD NOPRINT
END
-* OR
TABLE FILE HOLD
PRINT SORTFIELD FIELD1 FIELD2
BY RANK
END
This is what I have currently working... might not be the prettiest solution but I know it works here.

Leo

This message has been edited. Last edited by: <Mabel>,

That works great ... thanks!!

(IBI should really fix the RANK function, or at the very least offer an option or different function to do it this way.)

Yeah I agree... I'm not sure what is the logic of having a ranking system that doesn't rank properly... I have not found any use for it anyways.

Has anyone entered a case for this already?

Just thought I'd give an update on the Ranking code with a more efficient piece of code that does not require 2 table calls

TABLE FILE TNAME
PRINT COMPUTE RANKNUM/I6 = RANKNUM + 1; NOPRINT
COMPUTE RANK/I6 = IF SORTFIELD EQ LAST SORTFIELD THEN LAST RANK ELSE RANKNUM;
SORTFIELD FIELD1 FIELD2
BY HIGHEST 100 SORTFIELD NOPRINT
END

By using the compute you can't sort by the Rank variable, but it avoids making 2 table calls and speeds things up when the reports are large.

Leo

So does something like this not accomplish the desired output? If there are values that are a "tie" they all fall under the same rank number as I understand it.

TABLE FILE CAR
SUM
SALES
RANKED AS 'RANK' BY TOTAL
HIGHEST 3 SALES NOPRINT
BY
CAR
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT HTML

The ties do get the same value but the difference is exactly what TerryW pointed out,

quote:

RANK Value Expected RANK
---- ----- -------------
1------99------1
2------98------2
3------96------3
3------96------3
4------91------5
5------89------6

The following value will end up being the next number in the sequence instead of the true value. This also causes problems when your trying to display something like the Top 5 sales or something and there are ties. As you can see if the RANK is just used, you'll end up with 6 values that are ranked 5 and under.

The code I provided takes into consideration that if there is a tie, that the following number should receive what it actually is instead of the following number after the tie.

Leo