Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi

Read-Only Read-Only Topic
Go
Search
Notify
Tools
RANKING
 Login/Join
 
<We_Focus>
posted
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
 
Report This Post
<Grzegorz>
posted
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"/-->




This message has been edited. Last edited by: <Mabel>,
 
Report This Post
<JG>
posted
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.
 
Report This Post
Silver Member
posted Hide Post
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?
 
Posts: 40 | Registered: March 10, 2004Report This Post
Gold member
posted Hide Post
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>,
 
Posts: 96 | Location: Winnipeg, Manitoba, Canada | Registered: January 22, 2004Report This Post
Silver Member
posted Hide Post
This looks more like a LIST than a RANK, and does not provide for ties. Unless I am interpreting it incorrectly Eeker
 
Posts: 40 | Registered: March 10, 2004Report This Post
Gold member
posted Hide Post
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>,
 
Posts: 96 | Location: Winnipeg, Manitoba, Canada | Registered: January 22, 2004Report This Post
Silver Member
posted Hide Post
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.)
 
Posts: 40 | Registered: March 10, 2004Report This Post
Gold member
posted Hide Post
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?
 
Posts: 96 | Location: Winnipeg, Manitoba, Canada | Registered: January 22, 2004Report This Post
Gold member
posted Hide Post
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
 
Posts: 96 | Location: Winnipeg, Manitoba, Canada | Registered: January 22, 2004Report This Post
Gold member
posted Hide Post
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
 
Posts: 55 | Registered: May 22, 2003Report This Post
Gold member
posted Hide Post
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
 
Posts: 96 | Location: Winnipeg, Manitoba, Canada | Registered: January 22, 2004Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders