Tables: Create table for each unique value

Use this action to filter a data table by each unique value in a column, and create a new data table with all rows for each unique value.

Use case

This action can quickly split up a data table based on unique values. For example, split up a data table with regional sales information by a region column to create new tables for each region. This will create new tables where all of the EMEA, APAC, and AMER rows are segmented into 3 different tables.

How to configure this action

Given a data table with fruit type, price, and quantity, we will create new data tables based on the quantity of each fruit, grouping the unique values into new tables:

Fruit Price Quantity
Apple 10 10
Banana 40 10
Pear 20 2
Orange 10 2
Mango 12 5
Durian 17 5
Coconut 11 5

Set the Column name to Quantity to segment the table based on quantity values.

The result will be a data table field with 3 rows for the 3 unique quantity values, 10, 2, and 5. Each row in the output table lists the unique value, number of rows where the value was the same, and the new table’s ID:

screen readers look here

Each data table ID in the Table ID column is the ID, and a link to, each new table. For example, ID1 is a separate data table with just the rows where the quantity was 10, and any row with a different value is not included in the this file.

  • ID1 only has the rows where quantity is 10
  • ID2 has the rows where quantity is 2
  • ID3 where quantity is 5.

Fields for this action

  • Data table

    • Select a table to segment from a list of all tables available on your team. The list only includes tables you have permission to view.
      • You can also reference a table stored in a field. Change the left hand drop-down to Use table via field then select from any field that is part of the process. Learn more.
      • If necessary, you can enter the Table ID directly. Change the left hand drop-down to Use table by ID then enter the ID manually. Learn more.
  • Column name

    • Enter the column name to segment by each unique value. In the example above, Quantity, would be the column name used to segment the table.
  • Output field name

    • Enter the name of the field in which the result should be saved.
    • The step name is used by default.

What will this output?

This action will create new tables for each unique value in a data table. The action outputs a data table field that lists these unique table’s ID in a field with the output field name defined during configuration.

This action can segment a data table of up 10,000 rows. Any table with more than 10,000 rows will be partially segmented up to this limit. The excess rows will not be segmented.

Output fields for this action

  • Output field name

    • The output will be a data table field with a row for each unique value, the count of unique values, and the table ID where each are stored.
    • Unique Value Count Table ID
      A unique value The count of unique values moved to the new table The new data table ID.
  • Segmentation status

    • This field will return the status of the action.
      • If the action was able to fully segment the data table, the status will be Full.
      • If the limit is reached, the table is segmented up to the 10,000th row, the status will be Partial.

Get help with a problem or question

If something’s not working as expected, or you’re looking for suggestions, check through the options below.

Why do some column names not work?

Enclose individual column names and values in quotation marks (""), if they contain special characters like commas, leading/trailing whitespace, and newlines. For example:

  • If the column name is $Weekly Report,,,, enter the column name as "$Weekly Report,,,", with quotation marks.
  • If you want to use the field reference {{tablecolumn}} to dynamically reference the column name, enter it as "{{tablecolumn}}"

If the column name contains a quotation mark, escape each quotation mark with quotation marks, for example: if the column name is "Column name", enter it as """Column name""".

Sorry about that. What was the most unhelpful part?

Thanks for your feedback

We update the Help Center daily, so expect changes soon.

Link Copied

Paste this URL anywhere to link straight to the section.

Need more help?

If you're signed in to Catalytic Community, you can ask other users a question. You'll be redirected to Community where you can add more info.