`<willbe>`

Define a new computed column in an existing worksheet with a value expression.

Description

<willbe> creates a new computed column in a table or worksheet. The contents of the computed column are defined in the value attribute. The column name is defined by the name attribute, and the column heading is defined by the label attribute.

Syntax

<willbe name="[COLUMN_NAME]" 
        value="[VALUE_EXPRESSION]"
        label="[COLUMN_HEADING]" 
        format="[FORMAT_VAR]" 
        spflag="[1|0]"
        insertafter="[PREV_COLUMN_NAME]"
        materialize="[0|1]" 
        replace="[0|1]" 
        filename="[INTERNAL_FILENAME]"
        compression="[none|fast|faster|fastest|small|smaller|smallest]"
        index="[0|1]" 
        desc="[LONG_DESCRIPTION]"
        job_responsibility="[0|1]"/>

Attributes

name

The name of the computed column to be defined.

This is the column name, which is used by the system to identify the column. Column names in 1010data may not contain any spaces or special characters, and must begin with a letter.

value

The value of the computed column as an expression.

Note: value is required.

label

Provides the column heading for the new column.

If you would like the new column to replace an existing column, specifying a label value of "&&" makes the new column inherit the label of the column upon which it is based. See the example below.

If you would like the heading to span more than one line, use ` to separate the lines (e.g., Date of`Last Visit).

format

The format for the column (see Display formats).

The default is determined by the type of column.

desc

Accepts a string that specifies the description for the column.

If this attribute is not specified, the value expression is used by default.

fixed

Accepts 0 for off (default) and 1 for on.

The first fixed column in a query will be placed at the left-most position of the table and hold its position in the displayed columns even when scrolling to the right. Multiple fixed columns will be fixed left to right in the order that they appear in the query.

(Available as of prod-9)

spflag

Indicates whether special values should be treated as N/As when evaluating the value expression.

The default is 0 (i.e., do not treat special values as N/As).

insertafter

Accepts a string containing the column name after which the new computed column should appear.

The default is to insert the new column at the end of the table.

materialize

Indicates whether the new computed column should be materialized as a physical column in the table.

The default is 0 (i.e., do not materialize).

Note: You must own the table to materialize a column in it.

Note: Do not use the variable {@now_} in any materialize operation. {@now_} will break your cache and may cause your query to be submitted more than once.

Attributes when `materialize="1"`

The following attributes are only valid when materialize="1":

replace

Indicates whether the new computed column should be allowed to replace an existing column in the table.

The default is 0 (i.e., do not replace).

Note: When using <willbe name="foo" materialize="1" replace="1"/>, any columns whose values are dependent on the column being replaced will be updated according to the new values in the materialized column.

filename

Specifies a specific internal filename to use for the column.

Note: This is advanced functionality that requires special privileges.

compression

Specifies the compression type to use for the column.

Possible values are:

none
fast
faster
fastest
small
smaller
smallest

The default is faster.

Note: This is advanced functionality that requires special privileges.

index

Creates an index for the column.

This allows for increased selection performance on the resulting column at the cost of storage.

The default is 0 (i.e., do not create an index).

Note: To index an existing column, set value="" (i.e., leave the value expression empty).

job_responsibility

Setting job_responsibility="1" prevents synchronization of the table being materialized on an environment configured for High Availability (HA). This is assuming a job on the other HA sites will also be performing the same materialize, so synchronization will be unnecessary. Does nothing in an environment not configured for HA.

The default is 0.

(Available as of version 9.36)

Example

In this example, assume the "current" table is a sales detail table.

<base table="pub.demo.retail.item"/>
<willbe name="margin" value="sales-cost" label="Margin" 
 format="type:nocommas;dec:2" spflag="1"/>

Example: Creating a replacement column

In the following example, we create a column to replace transid. We use '&&' as the label name, so that the new column inherits the column label of the column upon which it is based. In this case, the label of the new column will be Transaction ID.

<base table="pub.demo.retail.item"/>
<willbe name="newtransid" value=transid label="&&"/>

Example: Inserting a column after another column

In the following example, test1 is a column added to the end of the table (the default), and test2 is a column inserted after column a, using the insertafter attribute:

<table cols="a,b,c">
a,b,c
</table>
<willbe name="test1" value="'t1'"/>
<willbe name="test2" value="'t2'" insertafter="a"/>

<willbe>