How can I use Rank if I have more than 10000 group in a list?

Hi, I have a long list that has around 30000 line. soem of lines are duplicate and I need to deperate them. I wanted to use Rank function and group lines base on a property but I have 17000 actual group and Rank limit for group is 10000. How can I solve this?

Best Answer

  • DavidSmith
    Answer ✓
    Hi - I faced this same problem only yesterday.  It's a bit long winded, but should sort the issue
    1) You will have to split up the "grouping" list into smaller lists of 9999 each. You can do this by creating a module dimensioned by the "grouping" list.
    Add the following line items:
    Count: formula = 1
    Rank: formula = rank(count, ascending, sequential)
    List 1: Formatted as your grouping list, formula = if rank<10000 then name(item("grouping" list)) else blank
    List 2: Formatted as your grouping list, formula = if rank>10000 and rank <=19999 then name(item("grouping" list)) else blank
    etc....
    You can then create separate lists based on line item List1, List2 etc. via an import.
    2) in your ranking module create a line item for the original grouping list and a number of line items formatted again the newly created lists;these will be for the ranking groups.  Set the formulas of the to use a "finditem" against newly created list.  The groping list should only appear in one of the line item.
    3) Add a number of rank formulae using the ranking groups from 2) and then sum them together to give you the overall rank.

Answers

  • The idea is terrible. What should I do if the group number is variable? For example I have Client/Product combination that is >10000 in one production model and <10000 in the other one. What should I do then if the number of these combinations goes higher than 10000 at one point? And of cource there are problem with linking the artificial lists to real ones because some edits may occure to real list elements.

  • Thanks for your question - this is something we're actively investigating - please see my response here:

    RANK for more than 10000 groups