4 Replies Latest reply: Apr 13, 2018 8:06 AM by Abirami Palanisamy RSS

    Help on On demand app

    bhavani b

      Hi Experts,

       

      Can any one please help me on below requirement.

       

      Is it possible to apply the ODAG binding expressions on one fact table and one dimension table as seperate.

      Suppose there are two tables Products and Hours like below.Here Need to apply ODAG binding expression on START DATE from Products table and  STATUS,POSFLAG fields from Hours table is it possible or not please confirm.

      I have added some example code

       

       

      SET STARTDATE='';

      OdagBinding:

      LOAD * INLINE [

      VAL

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

      ];

      SET STARTDATE_COLNAME='STARTDATE';

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

       

       

      //Where Clause

      SET WHERE_PART = '';

      FOR EACH fldname IN 'STARTDATE'

        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);

       

      SET STATUS='';

      OdagBinding:

      LOAD * INLINE [

      VAL

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

      ];

      SET STATUS_COLNAME='STATUS';

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

       

      SET POSFLAG='';

      OdagBinding:

      LOAD * INLINE [

      VAL

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

      ];

      SET POSFLAG_COLNAME='POSFLAG';

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

       

      //Where Clause

      SET WHERE_PART1 = '';

      FOR EACH fldname IN 'STATUS','POSFLAG'

        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_PART1 );

       

       

      Products:

      Load

      STARTDATE,

      ID,

      SALES;

      Select

      STARTDATE,

      ID,

      SALES

      from ....

      $(WHERE_PART);

       

       

      Hours:

      Load

      EndDate,

      STATUS,

      POSFLAG;

      SELECT

      ID,

      EndDate,

      STATUS,

      POSFLAG

      From......

      $(WHERE_PART1);

        • Re: Help on On demand app
          Petter Skjolden

          The short answer is YES.

           

          There are no limitations in the ODAG functionality/methodology that prevents you from having selections from any field from any of the tables of the selection app. Qlik Sense does not in the data model have any notion whether a table is a fact table or a dimension table.

           

          There are a syntax errors in your load script that you need to fix. You have forgot an ending paranthesis/ellipses/bracket after the fieldname and before the beginning curly braces/brackets in all the three INLINE sections:

           

          $(ods_STARTDATE{"quote": "", "delimiter": ""}   

              should be    

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



          $(ods_STATUS{"quote": "", "delimiter": ""}

              should be    

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



          $(ods_POSFLAG{"quote": "", "delimiter": ""}

              should be    

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

           

            • Re: Help on On demand app
              bhavani b

              Hi Petter,

               

              Thanks for your reply.

              Could you please check the below thread On Demand App issue

               

              I am not able to apply date selections from Selected app to Template app.

               

              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: Help on On demand app
                  Petter Skjolden

                  If my previous response was helpful or answered your original question please mark it as such

                   

                  I am not able to help you debug your script in depth since I dont have access to ODAG at the moment. Some of the scripting templates in ODAG are quite convoluted and I at least need to run and debug to be able to get it to work correctly.

                   

                  Maybe someone else are able to give you in depth guidance. Good luck.

              • Re: Help on On demand app
                Abirami Palanisamy

                Yes It is possible bhavani..

                 

                Please see the below code. This will work as you wanted.

                 

                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;

                 

                 

                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;

                 

                 

                // 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 STARTDATE='';

                 

                OdagBinding:

                LOAD * INLINE [

                VAL

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

                ];

                SET STARTDATE_COLNAME='STARTDATE';

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

                 

                SET WHERE_PART = '';

                 

                FOR EACH fldname IN 'STARTDATE'

                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);

                 

                 

                SET STATUS='';

                OdagBinding:

                LOAD * INLINE [

                VAL

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

                ];

                SET STATUS_COLNAME='STATUS';

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

                 

                SET POSFLAG='';

                OdagBinding:

                LOAD * INLINE [

                VAL

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

                ];

                SET POSFLAG_COLNAME='POSFLAG';

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

                 

                SET WHERE_PART1 = '';

                 

                 

                FOR EACH fldname IN 'POSFLAG','STATUS'

                  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);

                 

                 

                Products:

                Load STARTDATE,ID,SALES;

                 

                Select STARTDATE,ID,SALES from ....

                 

                $(WHERE_PART);

                 

                Hours:

                Load EndDate,STATUS,POSFLAG;

                 

                SELECT ID,EndDate,STATUS,POSFLAG

                From......

                $(WHERE_PART1);