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.
Table of Contents
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:
Open the Data Preparation menu, open the rule Label & Insert text.
Insert Value ‘A’ in the new New Value field.
Selection the option Some cells in column. This makes Add Condition active. Select Add condition
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.Click on the Add another value.
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’.Add a third value ‘C’ and select All remaining cells in the column.
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.
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:
Insert ‘A’ when
Marks >= 70 AND Marks <= 100
Insert ‘B’ when
Marks >= 50 AND Marks <= 75
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.