When editing a cell in a column which semantic type is based on a
dictionary or a compound type, the application will automatically suggest values that
are part of this dictionary as you type, to ensure that the whole column follows the
same standard.
In this example, you are working on a dataset containing customer data, including US state
codes. Because all the occurrences of Texas have
not been correctly entered in the two-letter code format, part of the data is considered
invalid in the State column, as shown by the
quality bar. You are going to isolate the rows containing errors, edit one of the cells
with autocompletion, and apply the change to all identical cells to fix the whole column
in a single operation.
Before you begin
The autocompletion menu is only available if the semantic type of the column is based
on a dictionary of values or a compound type present by default in Talend Data Preparation, or that
you have created using Talend Dictionary Service. For
more information on how to create custom semantic types, or edit the existing ones, see
Enriching the semantic types libraries.
Procedure
In the State column, click the orange part of the
quality bar and click Select rows with invalid values for
State to apply a filter and isolate the rows with
Texas.
In the State column,
double-click one of the occurrences of Texas.
You can now edit the content of the cell. When you delete the previous
value, a drop-down list opens, suggesting all the values that are part of the
US State Code dictionary-based semantic
type, sorted alphabetically.
Because the code for Texas is not part of the first results
that are displayed, start typing Tto see which results
come up.
The suggestions will become more accurate as you type. But if
the expected value can already be found from the original list, you can directly
select it.
From this refined list, select the state code that corresponds to Texas, namely
TX.
Select the check box Apply to all
cells with this value that appears once you have finished typing
or selecting a value.
The change you have made in this cell will now also apply to
the other incorrect values.
Click Submit.
Results
Thanks to the suggestions, you are
ensured that the new value you applied is in the correct format. All the other incorrect
occurrences of Texas have been replaced by the
correct TX state code in a single operation. The
quality bar now indicates that all the data in the State column is valid.
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!
