How to check if values from one field exists in a different field in Alteryx

Let’s explore how to check if values from one field exists in a different field in Alteryx. During ETL process is common to have the need to scan two different fields to check if there is any value that is present in both fields.

Alteryx is a very powerful software that can automatically identify values that are repeated across two different fields , even if the two values are not identical but one is contained in the other.

Depending on the situation and the specific need we can handle this in two different ways:

• If we want to match the complete information from the two fields and we are not interested in working with the output of this check, we can use a Join tool (some records could be duplicated)
• If we accept matches of even just a part and we are interested in working with the output of this operation the best choice is an Append Fields tool

 

The starting field should appear as shown below

Join tool

Using a Join tool is the simplest and fastest way to entirely match two different fields if we are not worried about duplicates records.

1. Connect your workflow to both input connectors of a Join tool
2. Be sure that the two field you want to join has the same data type (string fields can be joined only with other string fields, numeric fields can be joined only with other numeric fields)
3. Configure the Join tool to match the two fields 
4. Use the embedded Select tool to eventually rename or delete some of the fields
5. In the left output connector , you will find all the values from the first field that have not found a match in the second one
6. In the right output connector , you will find all the values from the second field that have not found a match in the first one 
7. In the join output connector , you will find all the values from the first field that have found a match in the second one (if a value from the first field has found more that a match in the second one, that record will be duplicated to match all the corresponding values in the second field)

Append Fields tool

Using an Append Fields tool is very useful if we accept partial match or if we do not want to face the duplicates issue

1. Connect your workflow to both input connectors of an Append Fields tool
2. In the configuration panel choose the type of match that you want between:
   Beginning of the field -> a match will be returned only if, starting from the first character, a correspondence is found
   Any part of the field -> a match will be returned if the characters sequence of the first field is found in any part of the second one
   Entire field -> same behavior of the Join tool but no duplicates will be generated We will use ‘Entire field’ for this example
3. Select the fields you want to match:
   Find Within Field -> chose the field in which you want to search the values
   Find Value -> chose the field containing the values to match
4. You can select some advanced matching options if needed
5. Chose ‘Append Field(s) to Record’ to create a new field in your dataset that shows the matched values, in this way every match will be marked appending a new value in a new field, null values represent missed matches If you choose to append the same filed chosen as ‘Find Value’ any value from the first field that has been found in the second one will be marked

The ending field should appear as shown below

We have seen two different techniques to handle with null values in Alteryx.

1. Join tool if we are interested in complete matches and are not worried about duplicates records
2. Append Fields tool if we are interested in partial matches and just marking matched values, no duplicates will be generated

Continue reading:

Check out: Resolve duplicate column names in excel file with Alteryx