3 Replies Latest reply: May 2, 2018 11:41 PM by bhavani b 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
            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);