You can use Direct Discovery to load more than one table or view using ANSI SQL join functionality.
In a single chart, all measures must be derived from the same logical table in Qlik Sense, but this can be a combination of several tables from source linked via join statements. However, you can use dimensions sourced from other tables in the same chart.
For example, you can link the tables loaded with Direct Discovery using either a Where clause or a Join clause.
- Direct Discovery can be deployed in a single fact/multi-dimension in memory scenario with large datasets.
- Direct Discovery can be used with more than one table which match any of the following criteria:
- The cardinality of the key field in the join is low.
The cardinality of the key field in the join is high, DirectEnableSubquery is set to true and all tables have been joined with Direct Discovery.
For more information, see Using subqueries with Direct Discovery.
- Direct Discovery is not suitable for deployment in a Third Normal Form scenario with all tables in Direct Discovery form.
Linking Direct Discovery tables with a Where clause
In this example script, we load data from the database AW2012. The tables Product and ProductSubcategory are linked with a Where clause using the common ProductSubCategoryID field.
Product_Join:
DIRECT QUERY
DIMENSION
[ProductID],
[AW2012].[Production].[Product].[Name] as [Product Name],
[AW2012].[Production].[ProductSubcategory].[Name] as [Sub Category Name],
Color,
[AW2012].[Production].[Product].ProductSubcategoryID as [SubcategoryID]
MEASURE
[ListPrice]
FROM [AW2012].[Production].[Product],
[AW2012].[Production].[ProductSubcategory]
WHERE [AW2012].[Production].[Product].ProductSubcategoryID =
[AW2012].[Production].[ProductSubcategory].ProductSubcategoryID ;
Linking Direct Discovery tables with Join On clauses
You can also use Join On clauses to link Direct Discovery tables. In this example statement we join the SalesOrderHeader table to the SalesOrderDetail table via the SalesOrderID field, and also join the Customer table to the SalesOrderHeader table via the Customer ID field.
In this example we create measures from the same logical table, which means we can use them in the same chart. For example, you can create a chart with SubTotal and OrderQty as measures.