The following examples show some of the options when using custom SQL for Direct Query.
Example: Adding custom SQL definitions to the Direct Query model
In this example, custom SQL definitions for tables C, R, N are added to the data model in the Direct Query app along with two inter-table relationships. The use of the Coalesce function is used to check if a field value of N_NATIONKEY is equal to the value of C_NATIONKEY, or if both are equal to null.
Tip note
Coalesce is the recommended way to include a check to see if a field value is equal to null. For more information, see coalesce - script and chart function.
LIB CONNECT TO 'CustomSQL:Snowflake_example.com';
Section DirectQuery;
C:
Select C_NATIONKEY, Sum(C_ACCTBAL) as C_SUM from tpch1.customer group by C_NATIONKEY;
R:
SELECT R_REGIONKEY, R_NAME from tpch1.region;
N:
SELECT N_REGIONKEY, N_NAME, N_NATIONKEY from tpch1.nation;
Create Relationship Between R, N Matching R_REGIONKEY With N_REGIONKEY;
Create Relationship
Between Outer Joined N, Inner Joined C On
(coalesce([N_NATIONKEY], -1) = coalesce([C_NATIONKEY], -1));
Example: Adding tables with custom SQL to a data model made with Data model manager
In this example, the Direct Query data model is first constructed with the contents in the DirectQueryModel.main object, which was created with Data model manager. Then an additional table, C with the fields C_CUSTKEY and C_ACCTBAL, are added to the data model. A relationship is made between the field O_CUSTKEY in ORDERS with the field C_CUSTKEY in C.
LIB CONNECT TO 'CustomSQL:Snowflake_example.com';
// DMM created model portion includes Orders table.
IMPORT LIVE 'ModelName@obj://DirectQueryModel.Main';
Section DirectQuery;
C: Select C_CUSTKEY, C_ACCTBAL from tpch1.customer;
Create Relationship Between ORDERS, C on (O_CUSTKEY = C_CUSTKEY);
Example: Constructing a Direct Query data model with a custom SQL table
In this example, the Direct Query data model is constructed with a custom SQL table, C, with the renamed fields C_KEY and C_BAL. Next, IMPORT LIVE adds the data model created in Data model manager from DirectQueryModel.main. This data model contains the table Orders and has an existing relationship between Orders and Customer in that model. Then, the existing relationship between Customers and Orders is dropped and a new relationship is defined between Orders and C.
LIB CONNECT TO 'CustomSQL:Snowflake_example.com';
Section DirectQuery;
C: Select C_CUSTKEY as C_KEY, C_ACCTBAL as C_BAL from tpch1.customer;
// DMM created model portion includes Orders table.
IMPORT LIVE 'ModelName@obj://DirectQueryModel.Main';
// This relationship was defined using DMM between ORDERS and CUSTOMER, but I want to define my own.
Drop Relationship Between ORDERS, CUSTOMER;
Create Relationship Between ORDERS, C on (O_CUSTKEY = C_KEY);
Example: Constructing a data model including variables
In this example, the variable MULT is defined first with a value of 100. Then, it is applied as a multiplier to the sum of PS_Supplycost to calculate the field S.
LIB CONNECT TO 'CustomSQL:Snowflake_example.com';
Section DirectQuery;
LET MULT=100;
T1: SELECT PS_AVAILQTY AS C, SUM(PS_SUPPLYCOST) * $(MULT) AS S FROM "TPCH.01"."PARTSUPP" GROUP BY C;
Example: Constructing a data model with Qlik expressions
In this example, both variables and expressions are used to build the data model. In the table ORDERS_AGGREGATIONS, expressions are used to define the values in ORDERS_GROUP_TOTAL_PRICE. Whenever selections are made in a sheet that would impact the values in ORDERS_GROUP_TOTAL_PRICE, the expression will be re-evaluated on the data returned by the query to the database.
IMPORT LIVE 'ModelName@obj://DirectQueryModel.Main';
section DirectQuery;
let Aggr1 = 1;
let Aggr2 = 0;
[GROUPS]:
SELECT 0 as GROUP_CODE, 'NONE' as "GROUP"
UNION ALL
SELECT 1 as GROUP_CODE, 'ORDERSTATUS' as "GROUP"
UNION ALL
SELECT 2 as GROUP_CODE, 'ORDERPRIORITY' as "GROUP"
;
[ORDERS_AGGREGATIONS]:
SELECT 'BY ORDERSTATUS' as "GROUP_NAME",
"O_ORDERSTATUS" as "ORDERS_GROUP",
sum("O_TOTALPRICE") as "ORDERS_GROUP_TOTAL_PRICE"
FROM "TEST1"."TPCH.01"."ORDERS"
WHERE '$(=min({<GROUP_CODE={0,1}>}[GROUP_CODE]))' = '1'
GROUP BY 1,2
UNION ALL
SELECT 'BY ORDERPRIORITY' as "GROUP_NAME",
"O_ORDERPRIORITY" as "ORDERS_GROUP",
sum("O_TOTALPRICE") as "ORDERS_GROUP_TOTAL_PRICE"
FROM "TEST1"."TPCH.01"."ORDERS"
WHERE '$(=min({<GROUP_CODE={0,2}>}[GROUP_CODE]))' = '2'
GROUP BY 1,2;
;
[ORDERS_AGGREGATIONS_VARS]:
SELECT 'BY ORDERSTATUS' as "GROUP_NAME_V",
"O_ORDERSTATUS" as "ORDERS_GROUP_V",
sum("O_TOTALPRICE") as "ORDERS_GROUP_TOTAL_PRICE_V"
FROM "TEST1"."TPCH.01"."ORDERS"
WHERE $(=Aggr1) = 1
GROUP BY 1,2
UNION ALL
SELECT 'BY ORDERPRIORITY' as "GROUP_NAME_V",
"O_ORDERPRIORITY" as "ORDERS_GROUP_V",
sum("O_TOTALPRICE") as "ORDERS_GROUP_TOTAL_PRICE_V"
FROM "TEST1"."TPCH.01"."ORDERS"
WHERE $(=Aggr2) = 1
GROUP BY 1,2;
;
Did this page help you?
If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!
