Contents x
    Split Field
      • Dark
        Light
      Contents

      Split Field

        • Dark
          Light
        Article Summary

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

        Split Field Component

        The Split Field component lets users split up the value of a column in an input flow according to a specified delimiter. The delimiter can be any valid character or sequence of characters.

        Properties

        PropertySettingDescription
        NameStringA human-readable name for the component.
        Input ColumnSelectThe input field to split.
        DelimiterStringThe delimiter to split on.
        Unlike when loading text files, the delimiter is not limited to a single character.
        Output ColumnsPositionThe Position is the index number of the split field to extract.
        Note: this index starts at 1, not 0.
        Output ColumnThe new field name to hold the extracted data. All target field names are text, but if you are certain they represent, e.g., a Date or Number type, you can use the Convert Type component to cast the data type appropriately.
        Include Input ColumnBooleanChoose whether or not to keep the original field in the output. If you have extracted all the parts of the field, you may not need the original anymore.

        Example

        In this example, we have a table that is filled with YouTube video data. One of the fields used contains the datetime data type in spite of the time being redundant, since it is always set for midnight. We want to take this field and split it into year, month, and day, while completely dropping the time section, so that the data can be easily transformed later on. To do this, we use a Split Field component, shown below.

        The properties for the first Split Field component are shown below. In this component, we want to split up the years, months, and days, which we already know are separated by dashes, so we will set this as our delimiter. This delimiter only acts on the field(s) in the Input Column, which will be the StartDate field.

        The Output Columns property allows us to define the new fields created by splitting up the old one. This is done by mapping the positions of each value returned by the split to a named field as shown below. Note that the positions start from 1 and not 0.

        The original data is shown below. In it, we can see the 'startdate' field as expected.

        After passing through the first Split Field component, the data becomes as follows.

        Note the new fields that have been created in the process of splitting up 'startdate'. Also note that the day_drop field contains both the day and a redundant timestamp. The second Split Field component is set up similarly but acts on the day_drop field to split by spaces, and maps only the first field ('day') as an output, thus removing the timestamp completely.

        This can now be linked to a Table Output component, which will write the finished data into a table for later use.

        What's Next