Important Transform is only available in Pullup mode. If you are using Pushdown mode, proceed to the next available step.

Transform

Transform allows you to override existing columns or create new ones based on the data in existing ones by performing the following operations:

  • Convert a column to a date, string, integer, double, or long data type.
  • Concatenate 2 or 3 string columns.
  • Replace empty spaces in specified columns with NULL.
  • Perform window functions on groups of rows.
  • Add custom transform expressions.

Note Transform is disabled by default.

Steps

  1. On the stepper, click Transform.
    The Transform step opens.
  2. Select from the following options, as needed.
    3. Option Description
    Add Collibra DQ Date Column

    Adds a synthetic run date column.

    Tip This can be helpful when your file does not contain dates that are easy to handle.
    Flatten Refers to the level of nesting in your file.
    Multi-line Refers to the content of the file being more than one line.
    Zero Fill Null Replaces NULL values with 0.
    Replace all field values of [value] with NULL Replaces all values of the column you specify with NULL.
  3. To the right of the Transform screen, click + Add.
    The Add a Transform modal appears.
  4. Select one of the following options and follow the steps accordingly.
    • Override a Column
    • Create a new Column
    1. Click the Override a Column option.
    2. Click the Operation dropdown menu and select an option.
      3. Operation Description
      to Date Converts a string column to a date type column.
      to String Converts a column to a string type column.
      to Int Converts a column to an integer type column.
      to Double Converts a column to a double type column.
      to Long Converts a column to a long type column.
      Concat(X, Y)

      Concatenates two string type columns, where "X" represents the first column and "Y" the second. Select the columns from the Column dropdown menus to the right of the Operation dropdown menu.
      Concat(X, Y, Z) Concatenates three string type columns where "X" represents the first column, "Y" the second, and "Z" the third.

      Select the columns from the Column dropdown menus to the right of the Operation dropdown menu.
      Replace Replaces empty spaces in a specified column with NULL.
      Window Functions

      Performs window functions on groups of rows.

      Note This option requires additional selections outlined in the following steps.

      1. Select an option from the Function dropdown menu.
      2. Select a column from the Column dropdown menu.
      3. Optionally select the Group by checkbox, then select the column(s) to group the window function.
      4. Optionally select the Order by checkbox, then select the column(s) in the order you want them to appear.
      Custom Adds custom transform expressions. Manually update the Expression input field after selecting a column from the Column dropdown.
    3. Click the Column dropdown and select an option.

      4. Tip If you already selected the columns after choosing Window Functions or one of the Concat options, you can skip this step.

    4. Review or manually edit the Expression input field, as needed.
    1. Click the Create a new Column option.
    2. Enter a column name for your new column in the input field to the right of the Create a new Column option.
    3. Click the Operation dropdown menu and select an option.
      4. Operation Description
      to Date Converts a string column to a date type column.
      to String Converts a column to a string type column.
      to Int Converts a column to an integer type column.
      to Double Converts a column to a double type column.
      to Long Converts a column to a long type column.
      Concat(X, Y)

      Concatenates two string type columns, where "X" represents the first column and "Y" the second. Select the columns from the Column dropdown menus to the right of the Operation dropdown menu.
      Concat(X, Y, Z) Concatenates three string type columns where "X" represents the first column, "Y" the second, and "Z" the third.

      Select the columns from the Column dropdown menus to the right of the Operation dropdown menu.
      Replace Replaces empty spaces in a specified column with NULL.
      Window Functions

      Performs window functions on groups of rows.

      Note This option requires additional selections outlined in the following steps.

      1. Select an option from the Function dropdown menu.
      2. Select a column from the Column dropdown menu.
      3. Optionally select the Group by checkbox, then select the column(s) to group the window function.
      4. Optionally select the Order by checkbox, then select the column(s) in the order you want them to appear.
      Custom Adds custom transform expressions. Manually update the Expression input field after selecting a column from the Column dropdown.
    4. Click the Column dropdown and select an option.

      5. Tip If you already selected the columns after choosing Window Functions or one of the Concat options, you can skip this step.

    5. Review or manually edit the Expression input field, as needed.

  5. Click Save.
  6. Optionally repeat steps 3-5 to add additional transforms.
  7. Continue to the next step on the stepper or click the right arrow on the right side of the page.