On-demand App Generation(ODAG) in QlikSense

    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.

    1. Enable On Demand Service on QMC under On-demand app Service Tab.
    2. By default it was disabled we need to enable by clicking on checkbox.

     

     

    On-Demand App Scripting:

    1. On-Demand functionality contains Two apps called 'Selection App' and 'Template App'.
    2. On-demand selection apps must load data with a modest level of dimension granularity.
    3. On-demand template apps contain load scripts with data binding expressions used to formulate the queries made on the data sources.
    4. 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.

    1. Open an on-demand selection app(ProductSelectionapp.qvf) and select Edit.
    2. Select App navigation links from the panel on the left side.
    3. Click the Create new button to open the Create new On-demand app navigation link dialog.
    4. Name the new on-demand app navigation link as per your wish
    5. Select an On-demand template app(ProductTemplateapp.qvf) from the drop down
    6. Write an Expression that computes the total number of detail records i.e., SUM(TOTAL_LINES) which we have created in the above script
    7. Specify the Maximum row count.Note: The Maximum row count should be less than or equal to SUM(TOTAL_LINES) count
    8. Specify the Maximum number of generated apps.
    9. 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.
    10. 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.
    11. 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.