`g_splice(G;S;O;X;D;N)`

Returns a concatenated list of values ordered in a specified manner within a given group.

Function type

Vector only

Syntax

g_splice(G;S;O;X;D;N)

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. Rows that have N/A values in `O` are ordered before all others.
`X`	any	The column on which to apply the function A column name
`D`	text	A string used to separate the values Note: The string should be surrounded by single quotes. If `D` is omitted, the values are concatenated together without a delimiter.
`N`	integer	The maximum number of values in the result If the total number of values exceeds this value, the result is truncated. If `N` is omitted, all values are included.

Return Value

For every row in each group defined by G (and for those rows where S=1, if specified), g_splice returns a text value corresponding to the concatenation of all of the values of X in all rows that are in the same group as that row. The order of the values is determined by the values in O; if O is omitted, the values are presented in the order they appear in the table. The values are separated by the string specified by D.

Sample Usage

<base table="pub.doc.samples.ref.func.g_func_time_series_sample_usage"/>
<willbe name="g_splice_1" value="g_splice(state;include;order;value;',';20)"/>
<willbe name="g_splice_2" value="g_splice(state city;include;order;value;',';20)"/>

Example

Let's say we want a list of all the SKUs for each department that is in our Product Master table (pub.demo.retail.prod).

Create a computed column using the g_splice(G;S;O;X;D;N) function. Specify dept as the group (G), since we're grouping by department; specify sku as X since we want the list of SKUs; and leave the selection parameter (S) blank, since we want to take into account all of the SKUs in the table (not just a subset). For demonstration purposes, we'll order the SKUs by class by specifying class for the O parameter. We'll use a comma as the separator (D) and set the maximum number of values to 20 (N).

<base table="pub.demo.retail.prod"/>
<willbe name="skus_by_dept" value="g_splice(dept;;class;sku;',';20)" 
 label="SKUs by Dept"/>

The results from this will look like the following:

You can see from the example that the only SKU in department 14 is 366. In department 22, there are four SKUs appearing in the table in the following order: 3B7, 98A, 969, and 96A; however, since we specified them to be ordered by the value in the class column, g_splice orders them as 98A, 3B7, 969, and 96A, separated by commas.