Substitute

<< Click to Display Table of Contents >>

Navigation:  Reference > Transforms >

Substitute

Description

Substitute column values into text.

 

Inputs

One.

 

Example

To create SQL statements to insert 'Country', 'Year', 'Key' and 'Value' column values:

 

INSERT INTO mytable(Country,Year,Key,Value) VALUES ($(Country),$(Year),$(Key),$(Value));

 

csv to sql example

 

Options

Enter your substitution script into the Substitution script field.

Select a column from Insert variable to add that variable into the Substitution script field at the current cursor position.

Click the Evaluate button to evaluate your script over every row.

 

Notes

You can reference column values by their name (e.g. $(item cost) for the 'item cost' column) or index (e.g. $(1) for the first column). The column name is case sensitive. You will get a warning if more than one column has the same name.

The transform is calculated every time:

oThe Evaluate button is pressed.

oThe Substitute transform item is unselected in the Center pane and script changes have been made without the Evaluate button being clicked.

oThe item upstream of it changes.

The new column is added at the right end. You can change the column order with Reorder Cols and the column name with Rename Cols.

If you want to carry out your transform across more than one dataset, you should Join them first.

If you need to do something more complex than this transform allows, try the Javascript transform.