g_rankuniq(G;S;X)

Returns the dense rank of unique values within a given group, giving repeated values the same rank.

Function type

Vector only

Syntax

g_rankuniq(G;S;X)
t_rankuniq(X)

Input

Argument Type Description
G any A space- or comma-separated list of column names

Rows are in the same group if their values for all of the columns listed in G are the same.

If G is omitted, all rows are considered to be in the same group.

If any of the columns listed in G contain N/A, the N/A value is considered a valid grouping value.
S integer The name of a column in which every row evaluates to a 1 or 0, which determines whether or not that row is selected to be included in the calculation

If S is omitted, all rows will be considered by the function (subject to any prior row selections).

If any of the values in S are neither 1 nor 0, an error is returned.
X any numeric type A column name

Return Value

For every row in each group defined by G (and for those rows where S=1, if specified), g_rankuniq returns an integer value corresponding to the rank of X in the list of all valid (non-N/A) values of X within the group.

The largest value has rank 1, and subsequent (decreasing) values are ranked in increasing increments of 1 (e.g., 2, 3, 4).

Repeated values are assigned the same rank, and the value following a set of repeated values is ranked incrementally by 1.

If X is N/A, the result is N/A.

Sample Usage

<base table="pub.doc.samples.ref.func.g_func_sample_usage_two_values"/>
<willbe name="g_rankuniq_1" value="g_rankuniq(state;include;value1)"/>
<willbe name="g_rankuniq_2" value="g_rankuniq(state city;include;value2)"/>

Example

Let's say we want to rank the items for all the daily transactions from highest to lowest in terms of sales price for each store in our table, but let's say we want those items with the same sales price to have the same rank. We can easily do this using g_rankuniq(G;S;X).

Let's use the Sales Item Detail table (pub.demo.retail.item) to demonstrate the use of this function. When we open the table, it looks something like this:

Since we want to rank the transactions for every date, and we want to do that for each store, we'll specify the store and date columns for the G parameter. We'll omit the S parameter so that all of the rows will be considered, and we'll specify the sales column for X, since we want the ranking to be based on the sales prices of the items.

Let's create a computed column for the ranking:
<willbe name="unique_rank" value="g_rankuniq(store date;;sales)"/>

This will give us a column that looks like the following:

To see the transactions in ranking order for every date for each store, you would sort first on the rank column, then on the date column, and then on the store column. You could do this by clicking on the up arrow () at the top of each of those columns in that order. (See Sorting rows in the 1010data User's Guide for more information about sorting multiple columns.)

Alternately, you could sort those columns using the following Macro Language code:
<sort col="unique_rank" dir="up"/>
<sort col="date" dir="up"/>
<sort col="store" dir="up"/>

The table would then show all the transactions for each store ordered by date, and all the transactions for each date ordered by rank:

You can see that on 06/03/12, there are seven transactions that now appear in the table ranked from the one with the highest sales to the lowest. You'll also notice that there are two transactions with rank 4, since they have the same value in the sales column (1.1). The next value is given the rank 5.

Additional Information

  • The t_ version of this function defaults the G argument and omits the S argument. The default for G is set at table load time based on the organization of the table.