Label & Insert text

The Label & Insert text rule allows you to annotate data, by inserting values into a new or an existing column. You can also use conditions to insert data based on a criteria.

When to Use

• If you have empty cells or columns and you want to populate them.

• If you want to replace some data in a column.

• If you want to enter multiple values in a column based on a criteria.

Quick Start

Let us start with the following sample data:

Student	Marks
Alice	84
Bob	62
Mike	47

Say we would like to grade these students based on this criteria

• Grade A: 70-100

• Grade B: 50-69

• Grade C: 0-49

Once graded this is how your data should look like:

Student	Marks	Grade
Alice	84	A
Bob	62	B
Mike	47	C

As you notice here, there are some cells in column grade that contain A, some B and some contain C. These are arrived at based on condition in marks.

Here is how we can go about doing this:

1. Go to Transform > Label, Filter and Replace.

2. Select the Label and Insert Values function.

3. Insert Value ‘A’ in the new New Value field.

4. Selection the option Some cells in column. This makes Add Condition active. Select Add condition

5. Set a condition such that Marks >= 70 AND Marks is <=100, hit Done to close the condition. This sets your condition for Grade ‘A’. However not all is done. Too meet the goals, You need to add more conditions.

6. Click on the Add another value.

7. Add a second value ‘B’ and set a condition on that as well. The second condition would be Marks < 70 AND Marks >=50. This sets your condition for Grade ‘B’.

8. Add a third value ‘C’ and select All remaining cells in the column.

9. Provide a name for the new column that shall contain the values stated above. In this case, enter ‘Grade’ into the New column name field.

10. Click APPLY.

Supported Options

The following options are supported by the Label & Insert text function.

• New Value: Specify the values you would like to add. You can choose the destination of the values in the Apply result into section. Pay attention to the type of value and the destination column type. Only numeric values can be inserted into numeric columns and date values into date columns.

• Apply to: Specify if you would like to apply the data to All cells in the column or Some cells in the column.

• Add condition: If Some cells in the column is selected, this option will appear. See apply filters

• Edit condition: For editing a condition. You can also edit a condition if it has been already set.

• Delete condition: For deleting a condition. This is only visible for the last condition set in the set of values.

• Add another value: This allows you to add another value to the list. This is only enabled when you have already set a condition on the previous value.

• Apply result into: This option allows configuration of the destination of the results by this task. See result documentation.

Condition hierarchy

If multiple conditions are satisfied when using Label & Insert Text, the latest condition in the list takes precedence over the other conditions. For example, let us start with the following sample data:

Student	Marks
Alice	72
Bob	62
Mike	47

Assume that we insert Values into a new column ‘Grade’ with the following criteria:

1. Insert ‘A’ when Marks >= 70 AND Marks <= 100

2. Insert ‘B’ when Marks >= 50 AND Marks <= 75

3. Insert ‘C’ otherwise

The final data will appear as mentioned below:

Student	Marks	Grade
Alice	72	B
Bob	62	B
Mike	47	C

This is because the value 72 satisfies both these conditions: 70 <= 72 <= 100 and 50 <= 72 <= 75. This will cause the second criteria to take precedence.

Hence it is best to design your conditions so that any given row matches only one of your conditions.

See also

Result Column

Number Formatting

Date Formatting