Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
On-demand apps enable you to load and analyze big data sources in Qlik Sense. On-demand app generation service is available in Qlik Sense default services from September 2017 version.
To work with On Demand App Generation User should need Qliksense Server. ODAG won't work on QlikSense Desktop Version. On-demand selection and template apps require special load scripting.
Enable On-Demand App Service in QMC:
Before starting ODAG scripting we need to enable On-Demand App Service in QMC like below.
- Enable On Demand Service on QMC under On-demand app Service Tab.
- By default it was disabled we need to enable by clicking on checkbox.
On-Demand App Scripting:
- On-Demand functionality contains Two apps called 'Selection App' and 'Template App'.
- On-demand selection apps must load data with a modest level of dimension granularity.
- On-demand template apps contain load scripts with data binding expressions used to formulate the queries made on the data sources.
- A selection app can be linked to multiple template apps, and a single template app can be linked to by multiple selection apps. But the template app's data binding expressions must correspond to fields in the selection apps that link to it.
1. Selection App Script:
On-demand selection apps is nothing but our Final Project application it must load data with a modest level of dimension granularity.
ODBC Connect to ...
FactTable:
Load
PURCHASEDATE as [Purchase Date]
PRODUCTID as Product,
STORENAME as [Store Name],
STOREDESCRIPTION as [Store Description],
PRICE as Price,
TAX as Tax,
1 as TOTAL_LINES;
sql select
to_chat(pp.purchasedate,'DD/MM/YYYY') as purchasedate,
pp.productid,
s.storename,
s.storedescription,
Sum(pp.price) as price,
Sum(pp.tax) as tax
from product_Price pp
inner join store s on s.storeid = pp.storeid
Group by pp.productid,s.storename,s.storedescription;
Left Keep(FactTable)
DimensionTable:
Load
PRODUCTID as Product,
PRODUCTYTPE as [Product Type],
PRODUCTDESCRIPTION as [Product Description],
CUSTOMERNAME as [Customer Name],
CUSTOMERADDRESS as [Customer Address];
sql select
p.productid,
p.producttype,
c.productdescription,
ct.customername,
ct.customeraddress
from product_account p
inner join product_company c on c.productcompanyid = a.productcompanyid
inner join product_customer_type ct on ct.productcustomertypeid = a.productcustomertypeid;
2. Template App Script:
//Created subroutine for fields in DimensionTable
SUB ExtendWhere(Name, ValVarName)
LET T = Name & '_COLNAME';
LET ColName = $(T);
LET Values = $(ValVarName);
IF len(Values) > 0 THEN
IF len(WHERE_PART) > 0 THEN
LET WHERE_PART = '$(WHERE_PART) AND $(ColName) IN ( $(Values) )';
ELSE
LET WHERE_PART = ' WHERE $(ColName) IN ( $(Values) )';
ENDIF
ENDIF
END SUB;
SET PRODUCTYTPE = ;
SET PRODUCTYTPE = $(ods_Product Type); // Right hand side field should match with Load -UI field
SET PRODUCTYTPE_COLNAME='p.producttype'; // Right hand side field should match with database feild
SET CUSTOMERNAME = ;
SET CUSTOMERNAME = $(ods_Customer Name); // Right hand side field should match with Load -UI field
SET CUSTOMERNAME_COLNAME='ct.customername'; // Right hand side field should match with database feild
SET WHERE_PART = '';
FOR EACH fldname IN 'PRODUCTYTPE', 'CUSTOMERNAME'
LET vallist = $($(fldname));
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendWhere('$(fldname)','vallist');
ENDIF
NEXT fldname
TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART);
// Created Custom Sub Routine for Date Field
SUB ExtendWhere1(Name, ValVarName)
LET T = Name & '_COLNAME';
LET ColName = $(T);
LET Values = $(ValVarName);
IF len(Values) > 0 THEN
IF len(WHERE_PART1) > 0 THEN
LET WHERE_PART1 = '$(WHERE_PART1) AND ( $(Values) )';
ELSE
LET WHERE_PART1 = ' $(Values) ';
ENDIF
ENDIF
END SUB;
SET PURCHASEDATE = ; //In the Load Left hand side PURCHASEDATE mention at first,Second and Third Set variable names
SET PURCHASEDATE = $(ods_Purchase Date]); // The field inside the $() on the right-hand-side of the SET statement must match with the field from //selection app
SET PURCHASEDATE_COLNAME='PURCHASEDATE';
SET WHERE_PART1 = '';
//First Set Statement Variable field PURCHASEDATE need to mention at For Loop
FOR EACH fldname IN 'PURCHASEDATE'
LET vallist = $($(fldname));
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendWhere1('$(fldname)','vallist');
ENDIF
NEXT fldname
TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART1);
//Created subroutine for other fields in FactTable
SUB ExtendWhere2(Name, ValVarName)
LET T = Name & '_COLNAME';
LET ColName = $(T);
LET Values = $(ValVarName);
IF len(Values) > 0 THEN
IF len(WHERE_PART2) > 0 THEN
LET WHERE_PART2 = '$(WHERE_PART2) AND $(ColName) IN ( $(Values) )';
ELSE
LET WHERE_PART2 = ' WHERE $(ColName) IN ( $(Values) )';
ENDIF
ENDIF
END SUB;
SET PRODUCTID = ;
SET PRODUCTID= $(ods_Product); // Right hand side field should match with Load -UI field
SET PRODUCTID_COLNAME='pp.productid'; // Right hand side field should match with database feild
SET STORENAME = ;
SET STORENAME = $(ods_Store Name); // Right hand side field should match with Load -UI field
SET STORENAME _COLNAME='s.storename'; // Right hand side field should match with database feild
SET PRICE= ;
SET PRICE = $(ods_Price); // Right hand side field should match with Load -UI field
SET PRICE_COLNAME='pp.price'; // Right hand side field should match with database feild
SET WHERE_PART2 = '';
FOR EACH fldname IN 'PRODUCTID ', 'STORENAME ', 'PRICE'
LET vallist = $($(fldname));
WHEN (IsNull(vallist)) LET vallist = '';
IF len(vallist) > 0 THEN
CALL ExtendWhere2('$(fldname)','vallist');
ENDIF
NEXT fldname
TRACE Generated WHERE clause: ;
TRACE $(WHERE_PART2);
ODBC Connect to ...
FactTable:
Load
PURCHASEDATE as [Purchase Date]
PRODUCTID as Product,
STORENAME as [Store Name],
STOREDESCRIPTION as [Store Description],
PRICE as Price,
TAX as Tax
1 as TOTAL_LINES;
sql select
to_chat(pp.purchasedate,'DD/MM/YYYY') as purchasedate,
pp.productid,
s.storename,
s.storedescription,
Sum(pp.price) as price,
Sum(pp.tax) as tax
from product_Price pp
inner join store s on s.storeid = pp.storeid
$(WHERE_PART2) and pp.purchasedate <= to_Date($(WHERE_PART1),'DD/MM/YYYY') ;
Left Keep(FactTable)
DimensionTable:
Load
PRODUCTID as Product,
PRODUCTYTPE as [Product Type],
PRODUCTDESCRIPTION as [Product Description],
CUSTOMERNAME as [Customer Name],
CUSTOMERADDRESS as [Customer Address];
sql select
p.productid,
p.producttype,
c.productdescription,
ct.customername,
ct.customeraddress
from product_account p
inner join product_company c on c.productcompanyid = a.productcompanyid
inner join product_customer_type ct on ct.productcustomertypeid = a.productcustomertypeid
$(WHERE_PART);
Note: No Need to reload the Template app just model it. Then from selection app, we can reload our template app for the selected values by generating new application.
Create App Navigation Link In between Selection App and Template App
Once we have completed the Selection App and Template App building need to create App Navigation Link in Between these two apps like below.
- Open an on-demand selection app(ProductSelectionapp.qvf) and select Edit.
- Select App navigation links from the panel on the left side.
- Click the Create new button to open the Create new On-demand app navigation link dialog.
- Name the new on-demand app navigation link as per your wish
- Select an On-demand template app(ProductTemplateapp.qvf) from the drop down
- Write an Expression that computes the total number of detail records i.e., SUM(TOTAL_LINES) which we have created in the above script
- Specify the Maximum row count.Note: The Maximum row count should be less than or equal to SUM(TOTAL_LINES) count
- Specify the Maximum number of generated apps.
- In the drop-down menu to the right of the Retention time of generated app menu, select the unit of time for the retention period.The options for retention time are hours, days, or Never expires.
- In the Default view when opening generated apps menu, select the sheet to display first when the apps generated from the navigation link are opened.
You can select App overview or one of the sheets in the selection app from which the navigation link is created. - Select a stream from the Publish to drop-down menu where apps generated from the navigation link will be published.
12. Then do selections on ODAG Filters. When the ODAG2 button turns to green, then user able to generate the app.
13. Click on On-Demand Button >then click on ”i” icon>it will show you the fetching lines under status>click on generated app button>again click on “i” icon to close the window
14. Once the app generated click on open app.
