`g_rank(G;S;O;X)`

Returns the rank of every value within a given group.

Function type

Vector only

Syntax

g_rank(G;S;O;X)
t_rank(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.
`O`	integer	A space- or comma-separated list of column names that determine the row order within a particular group If `O` is omitted, the order is the current display order of the table. If any of the values in `O` are N/A, 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_rank 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 ranked according to the order of the group (i.e., by the values in O, if specified; otherwise by the current display order of the table).

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

Sample Usage

<base table="pub.doc.samples.ref.func.g_func_time_series_sample_usage"/>
<willbe name="g_rank_1" value="g_rank(state;include;order;value)"/>
<willbe name="g_rank_2" value="g_rank(state city;include;order;value)"/>

Example

Let's say you want to rank the items for all the daily transactions from highest to lowest in terms of sales price for each store in your table. You can do this using g_rank(G;S;O;X).

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

Since you want to rank the transactions for every date, and you want to do that for each store, you specify the store and date columns for the G parameter. You can omit the S parameter so that all of the rows will be considered, and you can omit the O parameter, so the ordering will be determined by the current display order of the table. And you can specify the sales column for X, since you want the ranking to be based on the sales prices of the items.

Let's create a computed column for the ranking:

<willbe name="rank" value="g_rank(store date;;;sales)"/>

This will give you 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 arrow () that appears when you hover over the column name. Then, select Sort Up from the drop-down list, for rank, date, and store, 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="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 05/18/12, there are five transactions that now appear in the table ranked from the one with the highest sales to the lowest. You'll also notice that the transactions with rank 3, 4, and 5 have the same value in the sales column (1.1), so they are ranked according to the same order they appeared in the original table; the transaction with SKU 96A appears before the one with SKU 969 and the SKU 3B7.

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.