Map Values
    • Dark
      Light

    Map Values

    • Dark
      Light

    Article Summary

    This article is specific to the following platforms - Snowflake - Redshift - Synapse - Delta Lake.

    Map Values Component

    The Map Values component replaces one value with another in the dataset based on a specified condition.

    As the data is passed through the component, rules are applied to specified columns to replace one value with another. Like the Fixed Flow component, this can be used to introduce mappings into the data; for example, mapping a non-descriptive code to a more human-readable name.


    Properties

    Snowflake Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    Value MapInput ColumnThe name of the column output from the previous component used for comparison.
    QualifierCan be either Is, or Not; used to invert the filter if required.
    ComparatorSelect the comparator.
    • Less than: the value in the Input Column must be less than the value specified in the Comparison Value Column.
    • Less than or equal to: the value in the Input Column must be less than or equal to the value specified in the Comparison Value Column.
    • Equal to: the value in the Input Column must be equal to the value specified in the Comparison Value Column.
    • Greater than: the value in the Input Column must be greater than the value specified in the Comparison Value Column.
    • Greater than or equal to: the value in the Input Column must be greater than or equal to the value specified in the Comparison Value Column.
    • Like: sets a case-sensitive match of the value in the Input Column and the pattern in the Comparison Value Column.
    • ILike: sets a case-insensitive match of the value in the Input Column and the pattern in the Comparison Value Column.
    • Similar to: matches the value in the Input Column with an SQL standard regular expression pattern in the Comparison Value column.
    • Null: checks whether the value of the Input Column is the SQL null value.
    • Blank: checks whether the value of the Input Column is an empty string.
    • Null or blank: checks whether the value of the Input Column is either the SQL null value or an empty string.
    Comparison ValueA value entered by the user for comparison.
    Then ValueA value entered by the user that will be added to the data when the comparison is matched.
    OtherVarcharA value entered by the user when none of the conditions are matched.
    Column NameStringThe name of the output field that will be passed to subsequent components.

    Redshift Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    Value MapInput ColumnThe name of the column output from the previous component used for comparison.
    QualifierCan be either Is, or Not; used to invert the filter if required.
    ComparatorSelect the comparator.
    • Less than: the value in the Input Column must be less than the value specified in the Comparison Value Column.
    • Less than or equal to: the value in the Input Column must be less than or equal to the value specified in the Comparison Value Column.
    • Equal to: the value in the Input Column must be equal to the value specified in the Comparison Value Column.
    • Greater than: the value in the Input Column must be greater than the value specified in the Comparison Value Column.
    • Greater than or equal to: the value in the Input Column must be greater than or equal to the value specified in the Comparison Value Column.
    • Like: sets a case-sensitive match of the value in the Input Column and the pattern in the Comparison Value Column.
    • ILike: sets a case-insensitive match of the value in the Input Column and the pattern in the Comparison Value Column.
    • Similar to: matches the value in the Input Column with an SQL standard regular expression pattern in the Comparison Value column.
    • Null: checks whether the value of the Input Column is the SQL null value.
    • Blank: checks whether the value of the Input Column is an empty string.
    • Null or blank: checks whether the value of the Input Column is either the SQL null value or an empty string.
    Comparison ValueA value entered by the user for comparison.
    Then ValueA value entered by the user that will be added to the data when the comparison is matched.
    OtherVarcharA value entered by the user when none of the conditions are matched.
    Column NameStringThe name of the output field that will be passed to subsequent components.

    Synapse Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    Value MapInput ColumnThe name of the column output from the previous component used for comparison.
    QualifierCan be either Is, or Not; used to invert the filter if required.
    ComparatorSelect the comparator.
    • Less than: the value in the Input Column must be less than the value specified in the Comparison Value Column.
    • Less than or equal to: the value in the Input Column must be less than or equal to the value specified in the Comparison Value Column.
    • Equal to: the value in the Input Column must be equal to the value specified in the Comparison Value Column.
    • Greater than: the value in the Input Column must be greater than the value specified in the Comparison Value Column.
    • Greater than or equal to: the value in the Input Column must be greater than or equal to the value specified in the Comparison Value Column.
    • Like: sets a case-sensitive match of the value in the Input Column and the pattern in the Comparison Value Column.
    • ILike: sets a case-insensitive match of the value in the Input Column and the pattern in the Comparison Value Column.
    • Similar to: matches the value in the Input Column with an SQL standard regular expression pattern in the Comparison Value column.
    • Null: checks whether the value of the Input Column is the SQL null value.
    • Blank: checks whether the value of the Input Column is an empty string.
    • Null or blank: checks whether the value of the Input Column is either the SQL null value or an empty string.
    Comparison ValueA value entered by the user for comparison.
    Then ValueA value entered by the user that will be added to the data when the comparison is matched.
    OtherVarcharA value entered by the user when none of the conditions are matched.
    Column NameStringThe name of the output field that will be passed to subsequent components.

    Delta Lake Properties

    PropertySettingDescription
    NameStringA human-readable name for the component.
    Value MapInput ColumnThe name of the column output from the previous component used for comparison.
    QualifierCan be either Is, or Not; used to invert the filter if required.
    ComparatorSelect the comparator.
    • Less than: the value in the Input Column must be less than the value specified in the Comparison Value Column.
    • Less than or equal to: the value in the Input Column must be less than or equal to the value specified in the Comparison Value Column.
    • Equal to: the value in the Input Column must be equal to the value specified in the Comparison Value Column.
    • Greater than: the value in the Input Column must be greater than the value specified in the Comparison Value Column.
    • Greater than or equal to: the value in the Input Column must be greater than or equal to the value specified in the Comparison Value Column.
    • Null: checks whether the value of the Input Column is the SQL null value.
    • Blank: checks whether the value of the Input Column is an empty string.
    • Null or blank: checks whether the value of the Input Column is either the SQL null value or an empty string.
    • Like: sets a case-sensitive match of the value in the Input Column and the pattern in the Comparison Value Column.
    • Similar to: matches the value in the Input Column with an SQL standard regular expression pattern in the Comparison Value column.
    Comparison ValueA value entered by the user for comparison.
    Then ValueA value entered by the user that will be added to the data when the comparison is matched.
    OtherVarcharA value entered by the user when none of the conditions are matched.
    Column NameStringThe name of the output field that will be passed to subsequent components.

    Strategy

    Generates a SELECT statement with a CASE statement in-line.

    Example

    This example uses the airport code to map the airport's full name onto the data set.

    This achieves the same goal as the example on the Fixed Flow component, but without the join.

    The component properties are set up as below.

    The Value Map is set up with the 5 mapped airports in-line.

    The sample data shows the new column added.


    What's Next