Define related columns

Some IQ Interface worksheets contain related columns. You can recognize related columns by the yellow-colored bar under the column header.

Some worksheets have pre-defined, related columns that are available to use immediately. In some cases, however, you must manually define a relationship between your target worksheet's primary data source and another worksheet's primary data source before you can add related columns from that other worksheet to the target worksheet.

Learn how to:

• Define a relationship between two worksheets
• Add related columns to a worksheet
• Edit the related column settings
• Delete related columns from a worksheet

Define a relationship between two worksheets

You can create a relationship between worksheets. The worksheets can be in the same or different workbooks.

1. In a worksheet or a workbook, select Properties.
2. In the left panel, select Relationships.

3. Select New.
4. From the Related Type dropdown, select Worksheet.
5. From the Related Workbook dropdown, select the desired workbook.
6. From the Related Worksheet dropdown, select a worksheet in the workbook.
7. From the Join Type dropdown, select a join type:
   • Left—Returns all records from this worksheet, and the matched records from the related worksheet.
   • Right—Returns all records from the related worksheet, and the matched records from this worksheet.
   • Inner—Returns records that have matching values in both worksheets.
   • Outer—Returns all records (both matched and unmatched) from both worksheets.
   • Cross—Returns each record from this worksheet combined with each record from the related worksheet.
8. Specify which Source Column in this worksheet should be joined to a corresponding Related Column in the other worksheet.
   Important

   To avoid configuration errors, only columns that match the data type of the Source Column are available in the Related Column dropdown. For example, you cannot join a column with a numeric data type to a column with a string data type​.

9. From the Operator dropdown, select the join condition:
   • For source columns with string and Boolean data types, select one of these operators:
     • Equals (=)
     • Not equals (!=)
   • For source columns with numeric, date, or datetime data types, select one of these operators:
     • Equals (=)
     • Not equals (!=)
     • Less than (<)
     • Less than or equal to (<=)
     • Greater than (>)
     • Greater than or equal to (>=)
10. Select Save. The primary data source for the other worksheet now appears in the gray-colored Relationships area.
11. Select Go back to Workbook.

Add related columns to a worksheet

If your worksheet has at least one relationship defined, you can add any column from the related worksheet as a related column.

1. In the worksheet, select Column and KPI Settings.
2. Select Manage Columns, then select Manage Related Columns.

3. In Manage Related Columns of your Worksheet:
   1. Select a worksheet, then select a related entity from the Columns list.
   2. Select Add for each related column you want to add.
   3. Select Save.
4. Hide the Column and KPI Settings panel, then drag the new column's header label to the position you want in the worksheet.

Edit the related column settings

1. In the worksheet, select Column and KPI Settings.
2. On the Column tab, select the column you want to edit.
3. Make changes in Column settings, then select Save.

Delete related columns from a worksheet

There are two different ways to remove related columns from the worksheet.

Hide a column but keep it in the worksheet

1. In a worksheet, select Column and KPI Settings.
2. On the Column tab, toggle Show on the column name.

Permanently delete a related column from the worksheet

1. In a worksheet, select Column and KPI Settings.
2. On the Column tab​, select Manage Columns, then select Manage Related Columns.
3. Clear the checkbox for each related column you want to remove from the worksheet.
4. Select Save.