 |
|||||
|
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. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| RANKING | Login/Join |
| <Grzegorz> |
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: This message has been edited. Last edited by: <Mabel>, | ||
|
| <JG> |
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. | ||
|
| Silver Member |
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? | |||
|
| Gold member |
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. LeoThis message has been edited. Last edited by: <Mabel>, | |||
|
| Silver Member |
This looks more like a LIST than a RANK, and does not provide for ties. Unless I am interpreting it incorrectly  | |||
|
| Gold member |
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. LeoThis message has been edited. Last edited by: <Mabel>, | |||
|
| Silver Member |
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.) | |||
|
| Gold member |
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? | |||
|
| Gold member |
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 | |||
|
| Gold member |
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 | |||
|
| Gold member |
The ties do get the same value but the difference is exactly what TerryW pointed out, 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 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |