article On-demand App Generation(ODAG) in QlikSense in Qlik Sense Documents

On-demand App Generation(ODAG) in QlikSense

pawwy1415 — Wed, 05 Feb 2020 18:03:29 GMT

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:

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.

Re: On-demand App Generation(ODAG) in QlikSense

kkkumar82 — Sat, 07 Jul 2018 16:49:26 GMT

Thanks for the good post kumar, just a simple question, What is the real use case of ODAG in real world ?

Is it for BigData related applications? can you just elaborate ?

Re: On-demand App Generation(ODAG) in QlikSense

kuczynska — Fri, 13 Jul 2018 10:02:04 GMT

Hi there - for us it was driven by Big Data challenges (and the fact that analyzing daily transactions was extremely difficult due to performance issues caused by data volumes), but I am sure you can use this in different situations too. I am sure some of your users would like to see the links between one app and another, and you can achieve this using ODAG as well. Hope this helps,

Good luck!

Micha

Re: On-demand App Generation(ODAG) in QlikSense

kkkumar82 — Mon, 16 Jul 2018 08:17:28 GMT

Thanks for the answering

Re: On-demand App Generation(ODAG) in QlikSense

kuczynska — Wed, 01 Aug 2018 13:01:09 GMT

Hi All,

One other thing we are currently working on is max number of apps that can be generated at the same time - as per below:

We are currently waiting on some confirmation from Qlik if there is any way to overcome this issue (without the need to increase the infrastructure by additional load balancing nodes and upgrade to the most recent release) as this is now vital to the success of our project. We have been told that the value of 10 is a pure limit of the service, but we are still hoping there hardcoded limit of 10 could be removed.

Anyone here maybe managed to find any workarounds?

Thanks,

Michalina

Re: On-demand App Generation(ODAG) in QlikSense

jonvitale — Wed, 01 Aug 2018 13:08:47 GMT

kuczynska‌

Do you know if there is any way to avoid hard coding the ods_variables in the template app (e.g., ods_Product Type). We want to, for example, load in the fields that users can make a selection on from an excel file.

I posed that question here, but got no reply, which makes me thing it is not possible.

Variable Field Names in On-Demand App Generation (ODAG)

Re: On-demand App Generation(ODAG) in QlikSense

catalystmichael — Mon, 08 Oct 2018 07:11:37 GMT

I've written a short blog post on ODAG with a script you can download. Hope it's useful:

https://adventuresinqlik.com/2018/10/08/on-demand-app-generation-odag/

Re: On-demand App Generation(ODAG) in QlikSense

jonvitale — Mon, 08 Oct 2018 13:00:35 GMT

Can you double-check this link?

Re: On-demand App Generation(ODAG) in QlikSense

Ricardo_Gerhard — Wed, 07 Nov 2018 16:51:25 GMT

Another way to easily build your own ODAG

https://github.com/QlikPreSalesDACH/odag_wizard

Re: On-demand App Generation(ODAG) in QlikSense

sri_c003 — Fri, 11 Jan 2019 17:27:51 GMT

Is there any limit on the number of apps that can be created this way?

Re: On-demand App Generation(ODAG) in QlikSense

MartinMasMontiel — Wed, 03 Apr 2019 13:24:15 GMT

For those having issues running this, when ExtendWhere is being called in the for loop you have to call '$(vallist)', not 'vallist' because you want to reference the variable.

Re: On-demand App Generation(ODAG) in QlikSense

sharishd — Mon, 22 Apr 2019 16:33:36 GMT

Hello, thanks for your detailed steps.

I am getting the error "The script of the template app has no on-demand app bindings" when I try to select the template app

Re: On-demand App Generation(ODAG) in QlikSense

amarti — Thu, 09 May 2019 17:13:24 GMT

Hi,

does anybody know if there is the option to create two ODAG buttons for different users, I mean enable them for different users/groups?

The two buttons will have the same template, but different options on expire date. I don't think they can be shown only to the respective group, like an "OMIT" on a field.

Thank you!

Alessandro

Re: On-demand App Generation(ODAG) in QlikSense

taha_mansoor — Thu, 08 Aug 2019 13:20:20 GMT

Hi ,

Can anyone please write the syntax for selections quantity constraint. In help/documentation it is written as

$(od_MYFIELD)[1+]

when I am applying on actual code like this

SET YEAR='';
OdagBinding:
LOAD * INLINE [
VAL
$(odso_Year)[1-]{"quote": "", "delimiter": ""}
];
SET YEAR_COLNAME='Year';

I am getting syntax error highlight by Qlik Sense script editor.

what is the correct syntax ? In all the examples so far I couldn't find the code for selections quantity constraint.

Thanks,

Taha

Re: On-demand App Generation(ODAG) in QlikSense

amarti — Thu, 08 Aug 2019 14:10:05 GMT

Hi Taha,

Try with $(od_Year)[1-]. Do you need at most one value or at least one?

BR,

Alessandro

Re: On-demand App Generation(ODAG) in QlikSense

taha_mansoor — Sun, 18 Aug 2019 16:08:02 GMT

Hi Alessandro,

I tried $(od_Year)[1-] still getting the syntax highlight with Red. However, it worked.

Re: On-demand App Generation(ODAG) in QlikSense

brandk — Wed, 05 Feb 2020 12:41:20 GMT

@pawwy1415 I have followed your method of creating on demand app. The app successfully generates the where clauses and applies them. My problem is I get the field not found error.I have can confirm that field names are entered correctly in the binding script.

Re: On-demand App Generation(ODAG) in QlikSense

brandk — Thu, 13 Feb 2020 06:53:52 GMT

Good day

Could I get assistance with the set part of the script as below. I want my On demand app to filter using YearMonth. My issue is that in line 3 right hand side, I am required to match YearMonth field with source field but my QVD source does not contain YearMonth field. Instead the YearMonth is being generated during load using a Date field from the source through command Date.autoCalender.YearMonth

1. SET YearMonth = ;
2. SET YearMonth = $(ods_YearMonth); // Right hand side field should match with Load -UI field
3. SET YearMonth_COLNAME=' '; // Right hand side field should match with database field