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 transform is used to generate indicator columns, which can be used in statistical analysis.

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

Transformation:

valuestocols col:Data value:'X'

Results:

DataHappyDogHappy_Happy_Dog
HappyX  
Dog X 
Happy Happy Dog  X

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

TokenRequired?Data TypeDescription
values tocolsYtransformName of the transform
colYstringName of source column
valueYstringString literal, column, or function call that defines the value to use as the indicator value in any newly generated column
defaultNstringString literal, column, or function call that defines the value to use to indicate a false match in any newly generated column
limitNinteger (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
YesString (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
YesString 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
NoString 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:

DateNameonboardingChecklist
4/4/16Bowie KuhnOrientation
4/4/16Happy ChandlerContact Info
4/4/16Bowie KuhnContact Info
4/4/16Bowie KuhnAcquire Computer
4/4/16Bud SeligProduct Training
4/4/16Bud SeligOrientation
4/5/16Happy ChandlerHR Policies Training
4/5/16Happy ChandlerOrientation
4/5/16Happy ChandlerAcquire Computer
4/5/16Bowie KuhnHR Policies Training
4/5/16Bud SeligHR Policies Training
4/5/16Bud SeligContact Info
4/6/16Happy ChandlerProduct Training

Transformation:

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:

Results:

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

DateNameonboardingChecklistOrientationContact_InfoAcquire_ComputerProduct_TrainingHR_Policies_Training
4/4/16Bowie KuhnOrientationyes    
4/4/16Happy ChandlerContact Info yes   
4/4/16Bowie KuhnContact Info yes   
4/4/16Bowie KuhnAcquire Computer  yes  
4/4/16Bud SeligProduct Training   yes 
4/4/16Bud SeligOrientationyes    
4/5/16Happy ChandlerHR Policies Training    yes
4/5/16Happy ChandlerOrientationyes    
4/5/16Happy ChandlerAcquire Computer  yes  
4/5/16Bowie KuhnHR Policies Training    yes
4/5/16Bud SeligHR Policies Training    yes
4/5/16Bud SeligContact Info yes   
4/6/16Happy ChandlerProduct Training   yes 

Example - Magazine subscriptions