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

Returns the first non-N/A value within a given group.

Function type

Vector only

Syntax

g_first(G;S;O;X)
t_first(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 simple type	A column name

Return Value

For every row in each group defined by G and ordered by O (and for those rows where S=1, if specified), g_first returns the first non-N/A value in the column listed in X. The result is the same data type as X.

If no rows have valid (non-N/A) values for X, the result is N/A.

Note: An empty string is not considered to be an N/A for the purposes of g_ (and t_) functions.

Sample Usage

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

Example

For this example, let's use the table pub.demo.retail.prod.

Let's create a new dept_na and deptdesc_na column, which have the same values as the dept and deptdesc columns but with some N/A values in them.

<willbe name="dept_na" value="if(dept=14;NA;dept)"/>
<willbe name="deptdesc_na" value="if(dept=14;NA;deptdesc)"/>

And let's display only those columns of interest to us:

<colord cols="dept,deptdesc,dept_na,deptdesc_na"/>

We might assume that g_first would return 22 for the dept_na column and "SNACKS" for the deptdesc_na column, since g_first returns the first non-N/A value within a given group. Let's see what we get if we run g_first on each of these columns:

<willbe name="dept_first" value="g_first(;;;dept_na)"/>
<willbe name="deptdesc_first" value="g_first(;;;deptdesc_na)"/>

We can see from this example that g_first does indeed return a 22 for the dept_na column. However, g_first returns the empty string for the deptdesc_na column. This is because the deptdesc_na column is a text column, and the empty string is not considered to be an N/A value for the purposes of g_ (and t_) functions.

If you wanted g_first to return the first value in deptdesc_na that was not an empty string, you could use the S argument with the g_first(G;S;O;X) function. First, create a computed column that contains a 1 for those rows in deptdesc_na that are not N/A:

<willbe name="not_na" value="deptdesc_na<>NA"/>

And then specify that column as the S argument to the g_first function so that it only considers those rows:

<willbe name="deptdesc_first_not_na" value="g_first(;not_na;;deptdesc_na)"/>

You can see that g_first now returns "SNACKS" for the deptdesc_na column:

Additional Information

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