View Source

For each unique value in a column, a separate column is created. For each row that contains the value in the source column, an indicator value is inserted in the new column.

This indicator value can be a literal value or the output of a function.
If no indicator value is generated, a null value is written.

This transform is used to generate indicator columns, which can be used in statistical analysis.

It evaluates entire cell values for uniqueness. It does not scan for individual elements in Object or Array data.
If a row in the source column contains a missing value, an indicator value is added in a new Empty column.
It is not appropriate for tabulating counts of strings or patterns in a column. See Countpattern Transform.

Optionally, you can specify a default value, which is applied to all non-indicator value cells in the new column.

NOTE: When this transform is applied in the data grid, it only identifies the unique values in the current sample. If there are other unique values in the entire dataset, new columns are not created for them when the transform is executed across the entire dataset.

Source:

Data
Happy
Dog
Happy Happy Dog

Transform:

valuestocols col:Data value:'X'

Results:

Data	Happy	Dog	Happy_Happy_Dog
Happy	X
Dog		X
Happy Happy Dog			X

valuestocols col:column_ref value:(expression) default:(expression) [limit:int_num]

Token	Required?	Data Type	Description
values tocols	Y	transform	Name of the transform
col	Y	string	Name of source column
value	Y	string	String literal, column, or function call that defines the value to use as the indicator value in any newly generated column
default	N	string	String literal, column, or function call that defines the value to use to indicate a false match in any newly generated column
limit	N	integer (positive)	Maximum number of columns to generate. Default is `50`.

col

Identifies the column to which to apply the transform. You can specify only one column.

Required?	Data Type
Yes	String (column name)

value

For the valuestocols transform, this parameter specifies the value to insert in each row of a generated column where the column name of the generated column appears in the same row of the source column. This value can be a string literal, a column reference, or a function.

Required?	Data Type
Yes	String literal, column reference, or function call

default

Optionally, this parameter can be used to specify the value to insert in each row of a generated column where the column name of the generated column does not appear in the same row of the source column. This value can be a string literal, a column reference, or a function.

If this parameter is not specified, a missing value is inserted.

Required?	Data Type
No	String literal, column reference, or function call

limit

The limit parameter defines the maximum number of columns to create from the unique values detected in the source column. If not specified, the limit is 50.

NOTE: Be careful setting this parameter too high. In some cases, the application can run out of memory generating the results, and your results can fail.

Required?	Data Type
No. Default value is `50`.	Integer (positive)

Example - Basic valuestocols

Source:

This dataset contains onboarding milestones for three employees who joined the company at the same time. The milestones were recorded and organized by date as individual items, so it's not easy to verify that all five milestones have been checked off for each employee:

Orientation
Contact Info
Acquire Computer
HR Policies Training
Product Training

Date	Name	onboardingChecklist
4/4/16	Bowie Kuhn	Orientation
4/4/16	Happy Chandler	Contact Info
4/4/16	Bowie Kuhn	Contact Info
4/4/16	Bowie Kuhn	Acquire Computer
4/4/16	Bud Selig	Product Training
4/4/16	Bud Selig	Orientation
4/5/16	Happy Chandler	HR Policies Training
4/5/16	Happy Chandler	Orientation
4/5/16	Happy Chandler	Acquire Computer
4/5/16	Bowie Kuhn	HR Policies Training
4/5/16	Bud Selig	HR Policies Training
4/5/16	Bud Selig	Contact Info
4/6/16	Happy Chandler	Product Training

Transform:

The following transform creates columns for each of the values in the onboardingChecklist column and adds a yes value where there is a match for the row:

valuestocols col: onboardingChecklist value:'yes'

Results:

In the generated columns, you can quickly assess whether all three employees have completed an individual onboarding item:

Bud Selig has not acquired his computer.
Bowie Kuhn has not had product training.

Date	Name	onboardingChecklist	Orientation	Contact_Info	Acquire_Computer	Product_Training	HR_Policies_Training
4/4/16	Bowie Kuhn	Orientation	yes
4/4/16	Happy Chandler	Contact Info		yes
4/4/16	Bowie Kuhn	Contact Info		yes
4/4/16	Bowie Kuhn	Acquire Computer			yes
4/4/16	Bud Selig	Product Training				yes
4/4/16	Bud Selig	Orientation	yes
4/5/16	Happy Chandler	HR Policies Training					yes
4/5/16	Happy Chandler	Orientation	yes
4/5/16	Happy Chandler	Acquire Computer			yes
4/5/16	Bowie Kuhn	HR Policies Training					yes
4/5/16	Bud Selig	HR Policies Training					yes
4/5/16	Bud Selig	Contact Info		yes
4/6/16	Happy Chandler	Product Training				yes

col

value

default

limit

Example - Basic valuestocols

Example - Magazine subscriptions