10 Replies Latest reply: Apr 9, 2018 7:39 AM by Abirami Palanisamy RSS

    On Demand App issue

    bhavani b

      Hi,

      kuczynska

      saurabhwadhwa

      erik.wetterberg

      abirami.palanisamy

      sureshqv

      omarbensalem


      Could you please help  me on below On demand Generation App issue.

      I am able to generate the  on demand apps from summary app but the selections are not applying in the generated On Demand apps getting total data.


      Could you please check the below Template app script once. Please let me know am i missed anything.

      1. I have created summary app like below

      By reloading getting 10000 records

      Main:

      LOAD

          APPLICATION,

          CustomerKey,

          SalesOrderNumber,

          SalesOrderLineNumber,

          OrderQuantity,

          DiscountAmount,

          SalesAmount,

          TaxAmt,

          Freight,

          OrderDate,

          ShipDate

      FROM [lib://IT Support/1.QVD\1.Extract\A1779.qvd] (qvd);

       

       

      2. Then created Template App

      By reloading getting 0 records. I think we no need to reload the Template app its mentioned in Qlik Help also.


      // DO NOT ALTER THIS SUBROUTINE

      SUB ExtendQVDWhere(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 mixmatch([$(ColName)],$(Values) )';

          ELSE

              LET WHERE_PART = ' WHERE mixmatch([$(ColName)],$(Values))';

          ENDIF

        ENDIF

      END SUB;

       

       

      // DO NOT ALTER THIS SUBROUTINE

      SUB ExtendSQLWhere(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;

       

       

      // DO NOT ALTER THIS SUBROUTINE

      SUB BuildValueList(VarName, TableName, ColName, QuoteChrNum)

        IF ($(QuoteChrNum) = 0) THEN

          LET LOADEXPR = 'Concat($(ColName),' & chr(39) & ',' & chr(39) & ') AS CombinedData';

        ELSE

          LET CHREXPR = ' chr(' & '$(QuoteChrNum)' & ') ';

          LET LOADEXPR = 'Concat( $(CHREXPR) & $(ColName) & $(CHREXPR)' & ',' & chr(39) & ',' & chr(39) & ') AS CombinedData';

        ENDIF

        _TempTable:

        LOAD $(LOADEXPR) Resident $(TableName);

        Let vNoOfRows = NoOfRows('_TempTable');

        IF $(vNoOfRows)> 0 THEN

          LET $(VarName) = Peek('CombinedData',0,'_TempTable');

        ENDIF

        drop table _TempTable;

        drop table '$(TableName)';

      END SUB;

       

      //binding Expression

       

      SET App='';

      OdagBinding:

      LOAD * INLINE [

      VAL

      $(odo_APPLICATION){"quote": "", "delimiter": ""}

      ];

      SET APP_COLNAME='APPLICATION';

      CALL BuildValueList('App', 'OdagBinding', 'VAL', 39); // 39 is for single quote wrapping values

       

      //Where Clause

      SET WHERE_PART = '';

      FOR EACH fldname IN 'APPLICATION'

        LET vallist = $(fldname);

        WHEN (IsNull(vallist)) LET vallist = '';

        IF len(vallist) > 0 THEN

          CALL ExtendQVDWhere('$(fldname)','vallist');

        ENDIF

      NEXT fldname

      TRACE Generated WHERE clause: ;

      TRACE $(WHERE_PART);

      //QVD

      Main:

      LOAD

          APPLICATION,

          CustomerKey,

          SalesOrderNumber,

          SalesOrderLineNumber,

          OrderQuantity,

          DiscountAmount,

          SalesAmount,

          TaxAmt,

          Freight,

          OrderDate,

          ShipDate

      FROM [lib://IT Support/1.QVD\1.Extract\Slaes.qvd] (qvd)

      $(WHERE_PART);

       

      But in the Generated app it contains the total data instead of selected data. Please let me know Am i missed anything.

       

       

      Thanks in advance.

        • Re: On Demand App issue
          Abirami Palanisamy

          Hi Bhavani,

           

          If you want to show only the selected values data in Template application please use $(ods_APPLICATION) instead of $(odo_APPLICATION) .


          $(odo_APPLICATION) - will take only the optional values of application not the selected values.


          PatternExpansion
          $(ods_REGION_CODE)Selected (green) values of REGION_CODE
          $(odo_REGION_CODE)Optional (white) values of REGION_CODE
          $(odso_REGION_CODE)Selected or optional values of REGION_CODE
          $(od_REGION_CODE)Same as $(ods_REGION_CODE), only selected (green) values of REGION_CODE

           

          Please use below code as binding expression.

           

          //binding Expression

           

          SET APPLICATION ='';

          OdagBinding:

          LOAD * INLINE [

          VAL

          $(ods_APPLICATION){"quote": "", "delimiter": ""}

          ];

          SET APPLICATION_COLNAME='APPLICATION';

          CALL BuildValueList('APPLICATION', 'OdagBinding', 'VAL', 39);

           

           

          I hope this will help you !

            • Re: On Demand App issue
              bhavani b

              Hi,

              Thanks for your reply.

              Could you please clarify my doubt.

              When I have loaded the Template app its fetching zero records.

              Do we need to reload the Template app or not please confirm.

                • Re: On Demand App issue
                  Abirami Palanisamy

                  hi,

                   

                  No need to reload template application. Just model it however you want.

                   

                  Then from selection application , you can reload your template app for the selected values by generating new application.

                  • Re: On Demand App issue
                    Abirami Palanisamy

                    Hi Bhavani ,

                     

                    As we discussed , I found a way to pass Date as well as other filters. Please see the below code.

                     

                    // GENERATING WHERE FOR DATE

                     

                    SUB ExtendSQLWhere(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  ( $(Values) )';

                        ELSE

                        LET WHERE_PART = '( $(Values) )';

                        ENDIF

                      ENDIF

                    END SUB;

                     

                     

                    // DO NOT ALTER THIS SUBROUTINE

                    SUB BuildValueList(VarName, TableName, ColName, QuoteChrNum)

                      IF ($(QuoteChrNum) = 0) THEN

                        LET LOADEXPR = 'Concat($(ColName),' & chr(39) & ',' & chr(39) & ') AS CombinedData';

                      ELSE

                        LET CHREXPR = ' chr(' & '$(QuoteChrNum)' & ') ';

                        LET LOADEXPR = 'Concat( $(CHREXPR) & $(ColName) & $(CHREXPR)' & ',' & chr(39) & ',' & chr(39) & ') AS CombinedData';

                      ENDIF

                      _TempTable:

                      LOAD $(LOADEXPR) Resident $(TableName);

                      Let vNoOfRows = NoOfRows('_TempTable');

                      IF $(vNoOfRows)> 0 THEN

                        LET $(VarName) = Peek('CombinedData',0,'_TempTable');

                      ENDIF

                      drop table _TempTable;

                      drop table '$(TableName)';

                    END SUB;

                     

                    SET DATE ='';

                    OdagBinding:

                    LOAD * INLINE [

                    VAL

                    $(ods_DATE){"quote": "", "delimiter": ""}

                    ];

                    SET DATE_COLNAME='DATE';

                    CALL BuildValueList('DATE', 'OdagBinding', 'VAL', 39);

                     

                    SET WHERE_PART = '';

                     

                    FOR EACH fldname IN 'DATE'

                      LET vallist = $(fldname);

                      WHEN (IsNull(vallist)) LET vallist = '';

                      IF len(vallist) > 0 THEN

                        CALL ExtendSQLWhere('$(fldname)','vallist');

                      ENDIF

                    NEXT fldname

                     

                    TRACE Generated WHERE clause: ;

                    TRACE $(WHERE_PART);

                     

                    // GENERATING WHERE FOR OTHER FIELDS

                     

                    SUB ExtendSQLWhere1(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 $(ColName) IN ( $(Values) )';

                        ELSE

                        LET WHERE_PART1 = 'WHERE $(ColName) IN ( $(Values) )';

                        ENDIF

                      ENDIF

                    END SUB;

                     

                    SUB BuildValueList1(VarName, TableName, ColName, QuoteChrNum)

                      IF ($(QuoteChrNum) = 0) THEN

                        LET LOADEXPR = 'Concat($(ColName),' & chr(39) & ',' & chr(39) & ') AS CombinedData';

                      ELSE

                        LET CHREXPR = ' chr(' & '$(QuoteChrNum)' & ') ';

                        LET LOADEXPR = 'Concat( $(CHREXPR) & $(ColName) & $(CHREXPR)' & ',' & chr(39) & ',' & chr(39) & ') AS CombinedData';

                      ENDIF

                      _TempTable:

                      LOAD $(LOADEXPR) Resident $(TableName);

                      Let vNoOfRows = NoOfRows('_TempTable');

                      IF $(vNoOfRows)> 0 THEN

                        LET $(VarName) = Peek('CombinedData',0,'_TempTable');

                      ENDIF

                      drop table _TempTable;

                      drop table '$(TableName)';

                    END SUB;

                     

                    SET TYPE ='';

                    OdagBinding:

                    LOAD * INLINE [

                    VAL

                    $(ods_TYPE){"quote": "", "delimiter": ""}

                    ];

                    SET TYPE_COLNAME='TYPE';

                    CALL BuildValueList1('TYPE', 'OdagBinding', 'VAL', 39);

                     

                    SET ID ='';

                    OdagBinding:

                    LOAD * INLINE [

                    VAL

                    $(ods_ID){"quote": "", "delimiter": ""}

                    ];

                    SET ID_COLNAME='ID';

                    CALL BuildValueList1('ID', 'OdagBinding', 'VAL', 39);

                    SET WHERE_PART1 = '';

                     

                    FOR EACH fldname IN 'TYPE','ID'

                      LET vallist = $(fldname);

                      WHEN (IsNull(vallist)) LET vallist = '';

                      IF len(vallist) > 0 THEN

                        CALL ExtendSQLWhere1('$(fldname)','vallist');

                      ENDIF

                    NEXT fldname

                     

                    TRACE Generated WHERE clause: ;

                    TRACE $(WHERE_PART1);

                     

                    SQL

                    select

                    distinct

                    ID,

                    TYPE,

                    to_char(DATE,'DD/MM/YYYY') as DATE

                    from abc

                    $(WHERE_PART1)

                    and to_char(DATE,'DD/MM/YYYY') in ($(WHERE_PART))

                     

                     

                    I hope this will work as you wanted.

                      • Re: On Demand App issue
                        bhavani b

                        Hi Abirami,

                         

                        Thanks alot for your help. but its still not working in my SQL query.

                        As you said earlier I have tested the date load manually.

                        First I have tested like this where to_char(p.date,'DD/MM/YYYY')  in ('02/02/2018') but its not loading the data.

                        so changed like below where p.date in  to_date('02/02/2018','DD/MM/YYYY'); then its loading the data.

                         

                        Manual Test:

                        Load

                        DATE,

                        STATUS,

                        TYPE,

                        POSFLAG

                        ID;

                        Sql Select

                        to_char(p.date,'DD/MM/YYYY') as date,

                        h.status,

                        h.posflag

                        from product p

                        inner join hours h on h.systemid = p.systemid and h.key=p.key

                        where p.date in  to_date('02/02/2018','DD/MM/YYYY');

                         

                        Next passed where part like where to_char(p.date,'DD/MM/YYYY')  in ($(WHERE_PART)); its not working

                        then tried where p.date in to_date($(WHERE_PART)); its also not working

                         

                        My SQL Table is like below.

                         

                        Load

                        DATE,

                        STATUS,

                        TYPE,

                        POSFLAG

                        ID;

                        Sql Select

                        to_char(p.date,'DD/MM/YYYY') as date,

                        h.status,

                        h.posflag

                        from product p

                        inner join history h on h.systemid = p.systemid and h.key=p.key

                        where p.date  in  to_date($(WHERE_PART));


                        Is ODAG supports the inner join and sql query with fields like p.date,h.status . Please confirm.


                        For DATE I have used the below ODAG expression

                        SET DATE ='';

                        OdagBinding:

                        LOAD * INLINE [

                        VAL

                        $(ods_DATE){"quote": "", "delimiter": ""}

                        ];

                        SET DATE_COLNAME='DATE';

                        CALL BuildValueList('DATE', 'OdagBinding', 'VAL', 39);

                          • Re: On Demand App issue
                            Abirami Palanisamy

                            Yes , It's supports all kind of joins. You can also pass where conditions like P.Date in ('01/01/2018') to SQL queries by using below code.


                            SET Date ='';

                            OdagBinding:

                            LOAD * INLINE [

                            VAL

                            $(ods_Date){"quote": "", "delimiter": ""}

                            ];

                            SET Date_COLNAME='P.Date';

                            CALL BuildValueList('Date', 'OdagBinding', 'VAL', 39);



                          • Re: On Demand App issue
                            bhavani b

                            Hi abirami.palanisamy,

                             

                            I have found the issue where it is. Could you please check the below points to help me.

                            For Testing purpose I have extracted the hours database table from SQL data connection without any joins i have tested only one table for checking date issue.

                             

                            In manual test the table is loading with below condition

                            lib connect to ....

                            LOAD

                            DATE

                            STATUS

                            POSFLAG;

                            Sql Select

                            to_char(DATE,'DD/MM/YYYY')  as DATE,

                            STATUS,

                            POSFLAG

                            where to_char(DATE,'DD/MM/YYYY')  in '02/02/2018';  .....Here without parenthesis ('02/02/2018') like '02/02/2018' is loading



                            By using below ODAG code the generate where clause is like DATE in ('02/02/2018')

                            next in the end of table when we pass to_char(DATE,'DD/MM/YYYY') in ($(WHERE_PART)) then its like

                            to_char(DATE,'DD/MM/YYYY') in (DATE in ('02/02/2018')) ---- so getting error

                            at $(WHERE_PART) need to pass only '02/02/2018' .Please help me to pass only selected date value under $(WHERE_PART).



                            SET DATE ='';

                            OdagBinding:

                            LOAD * INLINE [

                            VAL

                            $(ods_DATE){"quote": "", "delimiter": ""}

                            ];

                            SET DATE_COLNAME='DATE';

                            CALL BuildValueList('DATE', 'OdagBinding', 'VAL', 39);

                             

                            SET WHERE_PART = '';

                             

                            FOR EACH fldname IN 'DATE'  --- by this loop getting DATE in ('02/02/2018') is it possible to pass selected value without loop in                                                                              WHERE_PART

                              LET vallist = $(fldname);

                              WHEN (IsNull(vallist)) LET vallist = '';

                              IF len(vallist) > 0 THEN

                                CALL ExtendSQLWhere('$(fldname)','vallist');

                              ENDIF

                            NEXT fldname

                             

                            TRACE Generated WHERE clause: ;

                            TRACE $(WHERE_PART);


                            Thanks in advance.




                              • Re: On Demand App issue
                                Abirami Palanisamy

                                Hi Bhavani ,

                                 

                                you can use below code to pass only values in where condition.

                                 

                                The generated where condition will pass only the values like below.

                                 

                                If you select only one Date value ---- > ('02/02/2018')

                                If you select more than one Date values -----> ('02/02/2018','03/03/2018')

                                 

                                // Code to pass only values

                                 

                                SUB ExtendSQLWhere(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  ( $(Values) )';

                                    ELSE

                                    LET WHERE_PART = '( $(Values) )';

                                    ENDIF

                                  ENDIF

                                END SUB;

                                 

                                 

                                // DO NOT ALTER THIS SUBROUTINE

                                SUB BuildValueList(VarName, TableName, ColName, QuoteChrNum)

                                  IF ($(QuoteChrNum) = 0) THEN

                                    LET LOADEXPR = 'Concat($(ColName),' & chr(39) & ',' & chr(39) & ') AS CombinedData';

                                  ELSE

                                    LET CHREXPR = ' chr(' & '$(QuoteChrNum)' & ') ';

                                    LET LOADEXPR = 'Concat( $(CHREXPR) & $(ColName) & $(CHREXPR)' & ',' & chr(39) & ',' & chr(39) & ') AS CombinedData';

                                  ENDIF

                                  _TempTable:

                                  LOAD $(LOADEXPR) Resident $(TableName);

                                  Let vNoOfRows = NoOfRows('_TempTable');

                                  IF $(vNoOfRows)> 0 THEN

                                    LET $(VarName) = Peek('CombinedData',0,'_TempTable');

                                  ENDIF

                                  drop table _TempTable;

                                  drop table '$(TableName)';

                                END SUB;

                                 

                                SET DATE ='';

                                OdagBinding:

                                LOAD * INLINE [

                                VAL

                                $(ods_DATE){"quote": "", "delimiter": ""}

                                ];

                                SET DATE_COLNAME='DATE';

                                CALL BuildValueList('DATE', 'OdagBinding', 'VAL', 39);

                                 

                                SET WHERE_PART = '';

                                 

                                FOR EACH fldname IN 'DATE'

                                  LET vallist = $(fldname);

                                  WHEN (IsNull(vallist)) LET vallist = '';

                                  IF len(vallist) > 0 THEN

                                    CALL ExtendSQLWhere('$(fldname)','vallist');

                                  ENDIF

                                NEXT fldname

                                 

                                TRACE Generated WHERE clause: ;

                                TRACE $(WHERE_PART);

                                 

                                 

                                lib connect to ....

                                LOAD

                                DATE

                                STATUS

                                POSFLAG;

                                Sql Select

                                to_char(DATE,'DD/MM/YYYY')  as DATE,

                                STATUS,

                                POSFLAG

                                where to_char(DATE,'DD/MM/YYYY')  in $(WHERE_PART);

                                 

                                 

                                You mentioned without parenthesis only where condition is  working. But for me with parenthesis also it is working.

                                 

                                In this case when you want to pass more than one date value to where  how it will work without parenthesis ?


                                If your requirement is to select only one date and you don't want to pass values with parenthesis use below sub part.


                                SUB ExtendSQLWhere(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  ($(Values) )';

                                    ELSE

                                    LET WHERE_PART = ' $(Values) ';

                                    ENDIF

                                  ENDIF

                                END SUB;

                                  • Re: On Demand App issue
                                    bhavani b

                                    Hi Abirami,

                                     

                                    Thanks for your reply.

                                    My requirement is to pass only one selected date like this where to_char(DATE,'DD/MM/YYYY')  in '02/02/2018'; 

                                     

                                      • Re: On Demand App issue
                                        Abirami Palanisamy

                                        Okay. Then you can try below code. :-)

                                         

                                         

                                        SUB ExtendSQLWhere(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  ( $(Values) )';

                                            ELSE

                                            LET WHERE_PART = '$(Values) ';

                                            ENDIF

                                          ENDIF

                                        END SUB;

                                         

                                         

                                        // DO NOT ALTER THIS SUBROUTINE

                                        SUB BuildValueList(VarName, TableName, ColName, QuoteChrNum)

                                          IF ($(QuoteChrNum) = 0) THEN

                                            LET LOADEXPR = 'Concat($(ColName),' & chr(39) & ',' & chr(39) & ') AS CombinedData';

                                          ELSE

                                            LET CHREXPR = ' chr(' & '$(QuoteChrNum)' & ') ';

                                            LET LOADEXPR = 'Concat( $(CHREXPR) & $(ColName) & $(CHREXPR)' & ',' & chr(39) & ',' & chr(39) & ') AS CombinedData';

                                          ENDIF

                                          _TempTable:

                                          LOAD $(LOADEXPR) Resident $(TableName);

                                          Let vNoOfRows = NoOfRows('_TempTable');

                                          IF $(vNoOfRows)> 0 THEN

                                            LET $(VarName) = Peek('CombinedData',0,'_TempTable');

                                          ENDIF

                                          drop table _TempTable;

                                          drop table '$(TableName)';

                                        END SUB;

                                         

                                        SET DATE ='';

                                        OdagBinding:

                                        LOAD * INLINE [

                                        VAL

                                        $(ods_DATE){"quote": "", "delimiter": ""}

                                        ];

                                        SET DATE_COLNAME='DATE';

                                        CALL BuildValueList('DATE', 'OdagBinding', 'VAL', 39);

                                         

                                        SET WHERE_PART = '';

                                         

                                        FOR EACH fldname IN 'DATE'

                                          LET vallist = $(fldname);

                                          WHEN (IsNull(vallist)) LET vallist = '';

                                          IF len(vallist) > 0 THEN

                                            CALL ExtendSQLWhere('$(fldname)','vallist');

                                          ENDIF

                                        NEXT fldname

                                         

                                        TRACE Generated WHERE clause: ;

                                        TRACE $(WHERE_PART);

                                         

                                         

                                        lib connect to ....

                                        LOAD

                                        DATE

                                        STATUS

                                        POSFLAG;

                                        Sql Select

                                        to_char(DATE,'DD/MM/YYYY')  as DATE,

                                        STATUS,

                                        POSFLAG

                                        where to_char(DATE,'DD/MM/YYYY')  in $(WHERE_PART);