`<cols>`

Modifies the metadata of multiple columns at once, based on a query result or formatted tabular input.

Description

<cols> is a data-driven generalization of <col> and <colord> that takes a table2= argument and optionally contains a worksheet.

The table or worksheet should contain either column name (string type) or the column ord (integer) to identify specific target columns where metadata will be updated, as well as zero or more additional values which indicate metadata settings for each target column.

The target column is specified by the value in ord if name is not present, or is referenced by the value in name if it is present. Once the target column is identified, it will have its metadata modified by each non-NA/blank value in the label, fixed, width, format, and/or desc columns, as applicable. Each row in the worksheet specifies a modification to the current table's metadata.

If name is used for lookup, rows in the worksheet with names not found in the current table are ignored; if ord is used, the column indices must be unique and within the range of 1 ... the number of columns in the current table. If both the name and ord values are present in the worksheet, the columns are looked up by name and then rearranged according to ord.

The width column in the metadata table is handled specially; if a format column does not exist in the metadata table, it sets both the column's data width attribute and also the format width (width:x), without changing other aspects of the format. If a format column does exist, then width does not override the format width. The <col> operation has also been updated to support a width= attribute that behaves the same way (that is, <col name="name" width="24"/> sets the data width and format width to 24, as does <cols><table cols="name,width">name,24;</table></cols>).

Syntax

<cols table2=[TABLE_PATH]>
  <table cols="[ORD],[NAME],[COL_METADATA_1],[COL_METADATA_2]...">
    (integer),[COLUMN_NAME],[COL_METADATA_1_VALUE],[COL_METADATA_2_VALUE]...
</table>
</cols>

Attributes

table2: Path to table contaning metadata transformations on the current base table. Format explained in Example below. May be ignored and not mandatory if the operation contains <table> directly.

Possible values for `cols` attribute of `Table` element. See examples below.

ord: A unique row number for each row.
column_name: Identification of a particular column.
label: The new column heading for the column.
fixed: 1 if the column should be displayed as fixed (on the left side of the table display), 0 if not.
width: The set width of the column.
format: The new format for the column (see Display formats).
desc: The description for the column.

Example 1

Illustrates that each row may update a different subset of possible metadata values. Below note the following about each row in <table> :

The first row contains four elements: the first two of which define the order and column name to update. Since column name is present, the order value is used to rearrange the column position in the table. The last elements define the label and desc text to update, respectively.

In the second row, the first two elements again define the desired order and the specific column name to update, but the third position corresponding to label is blank, so label will not be altered while the new text for desc will update the metadata description.

In the third row, note that only the label is being updated while desc is excluded.

Finally, the last row updates neither label or desc.

Note: Make sure that the code for each row is on a single line.

<base table="pub.demo.weather.stations"/>
<cols>
  <table cols="ord,name,label,desc">
    4,name,City name,Name of the city where the station is located
    3,state,,Name of the US state or territory in which the city is located
    2,tz,Time Zone,
    1,id,,
  </table>
</cols>

Before:

After:

Example 2: `width` attribute

Demonstrates a custom operation to set the width of all columns to "fit" the actual maximum width of the data in each column.

<defop name="adjwidths">
  <declare type="contextual"/>
  <cols table2="*">
    <query name="q">
      <issue xml="{@preamble_}"/>
    </query>
    <for c="{@q._cols}">
      <willbe name="{@c}_width" value="width({@c})"/>
    </for>
    <tabu>
      <for c="{@q._cols}">
        <tcol source="{@c}_width" name="{@c}" fun="hi"/>
      </for>
    </tabu>
    <transpose namecol="name"/>
    <willbe name="width" value="m0"/>
    <colord cols="name,width"/>
  </cols>
</defop>
<base table="pub.demo.weather.stations"/>
<adjwidths/>

Example 2: custom width setting based on column data maximum

Example 3: Apply the metadata of `table` to the corresponding columns in `table2`

This example requires that the <table> from Exercise 1 has been saved locally, and the path to that table is set as the value of table2.

The columns used by <cols> are a subset of those produced by the <columns/> operation, and need to match what is defined in the table2 table. The same results are seen as in Example 1, via a different syntax:

<base table="pub.demo.weather.stations"/>
<cols table2="uploads.t520580279_cparry">
  <columns/>
  <colord cols="name,desc"/>
</cols>

The same transformation as Example 1, with an external table supplying the metadata rules.

Example 4: Use with `<amend>`

Changes every label to all uppercase.

<base table="pub.demo.weather.stations"/>
<cols table2="*">
  <columns/> 
  <amend name="label" value="strupcase(label)"/>
  <colord cols="name,label"/>
</cols>

Example 4 - set column labels to upper case

Example 5: Column labels

Uses the column name as the label for each column in the table that does not already have a label.

<base table="path.to.table1"/>
<cols table2="*">
  <columns/>
  <amend name="label" where="label=''" value="name"/>
  <colord cols="name,label"/>
</cols>

Example 5: use column name as the label for any column curretly without a label.

<cols>