Custom SQL examples for Direct Query

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 add to the data model intheDirect 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 ofC_NATIONKEY,or if both are equal to null.

LIB CONNECT TO ‘CustomSQL:Snowflake_example.com’;
Section DirectQuery;

C:
Select C_NATIONKEY,Sum(C_ACCTBAL) as C_SUM fromtpch1.customer group by C_NATIONKEY;

r:
SELECT r_rEGIONKEY,r_NAME fromtpch1.region;

N:
SELECT N_rEGIONKEY,N_NAME,N_NATIONKEY fromtpch1.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 inthe DirectQueryModel.main object,which was created with data model manager. Then an additional table,C with the fieldC_CUSTKEY andC_ACCTBAL,are added to the data model. A relationship is made between the field O_CUSTKEY inOrDErS with the field C_CUSTKEY inC.

LIB CONNECT TO ‘CustomSQL:Snowflake_example.com’;

// DMM created model portion includes order table.

IMPOrT LIVE ‘ModelName@obj://DirectQueryModel.Main’;

Section DirectQuery;

C: Select C_CUSTKEY,C_ACCTBAL fromtpch1.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 andC_BAL. Next,IMPOrT LIVE adds the data model created indata model manager fromDirectQueryModel.main. This data model contains the table order andhas an existing relationship between order andCustomer inthat model. Then,the existing relationship between Customers andorder is dropped anda new relationship is defined between order andC.

LIB CONNECT TO ‘CustomSQL:Snowflake_example.com’;

Section DirectQuery;

C: Select C_CUSTKEY as C_KEY,C_ACCTBAL as C_BAL fromtpch1.customer;

// DMM created model portion includes order table.

IMPOrT LIVE ‘ModelName@obj://DirectQueryModel.Main’;

// This relationship was defined using DMM between OrDErS andCUSTOMEr,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 : construct a data model with Qlik expression

In this example,both variables andexpressions are used to build the data model. In the table OrDErS_AGGrEGATIONS,expressions are used to define the values inOrDErS_GrOUP_TOTAL_PrICE. Whenever selections are made ina sheet that would impact the values inOrDErS_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;
;