1. Home
  2. Other Releases
  3. Cloud
  4. Guides
  5. Tours
  6. Concepts
    1. Aggregate filter
    2. Alias
    3. Apache Avro™
    4. Apache Kafka™
    5. Applied filter
    6. Base table
    7. Bookmark
    8. Business schema
    9. Business schema view
    10. Business schema view column
    11. Business schema view formula column
    12. Column
    13. Connector
    14. Dashboard filter
    15. Dashboard folder
    16. Dashboard runtime filter
    17. Data agent
    18. Data alert
    19. Data destination
    20. Date system variable
    21. Distinct filter
    22. Dynamic Field
    23. Expression
    24. External data source
    25. External session variable
    26. Filter expression
    27. Filter expression session variable
    28. Filter option
    29. Formula column
    30. Formula expression
    31. Full load
    32. Global variable
    33. Incorta Analyzer Table
    34. Incorta Analyzer Table column
    35. Incorta Analyzer Table formula column
    36. Incorta Analyzer View
    37. Incorta Analyzer View formula column
    38. Incorta SQL Table
    39. Incorta SQL View
    40. Individual filter
    41. Insight filter
    42. Internal query expression
    43. Internal session variable
    44. Join
    45. Join condition
    46. Join condition column
    47. Join filter
    48. Join filter column
    49. Level based measure
    50. Load filter
    51. Local data file
    52. Local data folder
    53. Materialized View
    54. Materialized View Column
    55. Materialized View Formula Column
    56. Measure filter
    57. Miscellaneous system variable
    58. Multi-Source table
    59. Physical schema
    60. Physical schema table
    61. Physical schema table column
    62. Physical schema table formula column
    63. Physical schema table runtime security filter
    64. Presentation variable
    65. Prompt
    66. Query system variable
    67. Runtime business view
    68. Runtime business view formula column
    69. Runtime security filter
    70. Session variable
    71. Shared Storage
    72. Upgrade Readiness
    73. Variable
    74. Visualization
  7. Tools
  8. References
  9. Integrations
  10. Data applications
  11. Releases

Concepts → Join Filter

About a join filter

A join filter is a filter expression for a Parent entity object in a join. The filter expression functions to filter rows in the Parent entity object.

As such, a join filter often functions to define the uniqueness of Parent entity rows. This is especially helpful when the Parent entity does not have a Key column or the Key column is not specified in the join condition.

In this regard, a join filter is synonymous with the ON clause in a SQL Join statement. Although both the join filter and join condition are synonymous with the ON clause in a SQL statement, the join condition compares the values of the two columns in the join using the condition operator while the join filter compares the value of a column from the Parent entity object with a specified value (except for the First Version and Last Version operators as these behave differently).

Note

A join filter does not affect a self-join, that is, a join where the same entity object is both the child and the parent.

The Loader Service evaluates the join filter expression and creates a direct data map when saving the join details or when loading the related physical schema(s) or entity object(s). The Analytics Service reads the join direct data map for queries that reference the join entities.

The join filter context

You use the Join Editor to manage filters for a given join. You have to be in a group with the Schema Manager or the SuperRole role to access the Schema Manager. You also need to either own the physical schema of the Child object or have Edit access rights to it in order that you can access the Join Editor and manage the physical schema joins.

In the case of a cross-schema join, you need to own both physical schemas or, at least, have Edit access rights to the physical schema of the Child object and View access rights to the physical schema of the Parent object.

Note

A join that you create between entity objects in different physical schemas shows in the Schema Designer for both physical schemas; however, you can manage these joins only by using the Join Editor for the physical schema that has the Child entity object in the join.

The join filter syntax

A join filter expression consists of:

  • a filter column
  • a filter operator
  • a filter value

About the join filter column

For a join filter, you can reference only columns from the Parent entity object. The following are the types of columns that you can reference in a join filter.

  • Physical_Schema.Table.Column
  • Physical_Schema.Table.Formula_Column
  • Physical_Schema.Incorta_Analyzer_Table.Column
  • Physical_Schema.Incorta_Analyzer_Table.Formula_Column
  • Physical_Schema.Materialized_View.Column
  • Physical_Schema.Materialized_View.Formula_Column
  • Physical_Schema.Alias.Column
  • Physical_Schema.Alias.Formula_Column
  • Physical_Schema.Incorta_SQL_Table.Column (including computed columns)

To learn more, review Concepts → Join Filter Column.

Important

It is not recommended to reference a formula column in an entity object in another schema for the join filter column.

About the join filter operator

The following are the available operators to select from for a join filter operator:

  • <: less than
  • ≤: less than or equal to
  • =: equal to
  • ≠, !=, or <>: not equal to
  • >: greater than
  • ≥: greater than or equal to
  • IN: a value from an array of scalar values
  • First Version: the lowest value in the join filter value column for each group in the filter column
  • Last Version: the greatest value in the join filter value column for each group in the filter column
Important

When using the First Version or the Last Version as the join filter operator, You need to select columns from the Parent object for both the filter column and the join filter value. In this case, the following applies:

  • The Loader Service groups data in the Parent entity object by the filter column and sorts data in each group by the join filter value column.
  • In the case of the First Version, the Loader Service retrieves only the row with the lowest (first) value in the join filter value column for each group that has a matching record in the Child entity object.
  • In the case of the Last Version, the Loader Service retrieves only the row with the greatest (last) value in the join filter value column for each group that has a matching record in the Child entity object.

About the join filter value

For a join filter value, you can use one of the following depending upon the operator that you select:

  • a scalar value for comparison operators (<, ≤, =, ≠, >, ≥)
  • an array of scalar values for the IN operator
  • a column in the Parent entity object only in the case of First Version and Last Version operators
Note

In addition to numeric and date scalar values, you can use comparison operators with string or text values. In such a case, the join filter column is sorted in an alphabetical order, and then compared to the entered value. For example, when you enter the following filter expression: State < Colorado, all cities that precede Colorado in the alphabetically ordered list will be retrieved; that is, Alabama, Alaska, Arizona, Arkansas, and California.

Important

A date needs to be in the same date format as the filterable column. In the Table Editor, you can preview the column details and view the sample data in order to discern the date format.

Important

You can reference a date system variable for the filter value. However, the technique serves only to capture a snapshot of the system variable value.The Loader Service evaluates the value when saving changes to a join in the Join Editor, during the load job for the entity object, or during the load job for the related physical schema.

Examples of join filters

The following table shows the available join filter operators, the supported values, and the effect of this filter on the rows retrieved from the Parent entity object after applying the join condition(s).

ColumnOperatorValueResult
Age<50All rows from the Parent table that have a matching for the join column(s) and where the Age column is less than 50 are retrieved.
Position≤SW EngineerAll rows from the Parent table that have a matching for the join column(s) and where the Position column is less than or equal to SW Engineer are retrieved.
Department=ITAll rows from the Parent table that have a matching for the join column(s) and where the Department column is equal to IT are retrieved.
Country≠USAAll rows from the Parent table that have a matching for the join column(s) and where the Country column is not equal to USA are retrieved.
State>CaliforniaAll rows from the Parent table that have a matching for the join column(s) and where the State column is greater than California are retrieved.
Salary≥1000All rows from the Parent table that have a matching for the join column(s) and where the Salary column is greater than or equal to 1000 are retrieved.
CityINCairo,London,BostonAll rows from the Parent table that have a matching for the join column(s) and where the City column is Cairo, or London, or Boston are retrieved.
PositionFirst VersionSalaryFrom the Parent table, only the row with the lowest Salary per Position that has a matching record in the Child table is retrieved.
DepartmentLast VersionHire_DateFrom the Parent table, only the row with the latest (greatest) Hire_Date per Department that has a matching record in the Child table is retrieved.

Example of the Equivalent SQL Statement

The following is an example of the SQL statement equivalent to a join with one condition and a filter. Consider the sch_HR physical schema with the tbl_employees and tbl_departments tables. A join created with the following condition and filter details:

Join ElementDetails
Conditionsch_HR.tbl_employees.DepartmentID = sch_HR.tbl_departments.DepartmentD
Filtersch_HR.tbl_departments.DepartmentName IN ("HR","Sales")
SQL StatementSELECT sch_HR.tbl_employees.Name, sch_HR.tbl_departments.DepartmentName FROM sch_HR.tbl_employees LEFT OUTER JOIN sch_HR.tbl_departments ON sch_HR.tbl_departments.DepartmentName IN (“HR”,”Sales”) AND sch_HR.tbl_employees.DepartmentID = sch_HR.tbl_departments.DepartmentID
Query ResultA list with all employees from the Child table (tbl_employees). For each row in the Child table where the department is “HR” or “Sales”, the department name is retrieved from the Parent table (tbl_departments); otherwise, the DepartmentName column will be Null.

Additional Considerations

Adding a join filter to a join is different from a SQL statement that has a filter expression in a WHERE clause or an INNER JOIN.

In a physical schema, a join between Child and Parent entity objects is a Left Outer Join. In a SQL statement, the WHERE clause defines a filter expression that removes rows after the join operation. Because a physical schema join is a Left Outer Join, it is not possible to push the predicate into the join itself as it would be possible with an Inner Join.