1 Reply Latest reply: Feb 20, 2018 10:04 AM by Venkata Nerella RSS

    Simple way to create ODAG in Qliksense

    karthikeyan subramaniam

      Common sub routine

       

       

       

      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;

       

       

       

       

       

       

      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;

       

      ODAG Section

       

      SET Brand='';

      OdagBinding:

      LOAD * INLINE [

      VAL

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

      ];

      SET Brand_COLNAME='BrandName';

       

       

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

       

       

      SET WHERE_PART = '';

       

       

      FOR EACH fldname IN 'Brand'

        LET vallist = $(fldname);

        IF (IsNull(vallist)) THEN

          LET vallist = '';

        ENDIF

        IF (len(vallist) > 0) THEN

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

       

       

        ENDIF

      NEXT fldname

       

       

      main:

      LOAD

          BrandName,

          product,

          cost

        

      FROM [lib://Data/main.qvd]

      (qvd)$(WHERE_PART);

        • Re: Simple way to create ODAG in Qliksense
          Venkata Nerella

          Hey Karthik,

           

          Not sure if you posted a question or a suggestion on how to implement ODAG.

           

          A while ago,I asked this question

          On Demand App Question

           

          I think your way of implementing will help me achieve what i'm looking for..

          Sorry,Pretty new to this stuff and couldnt understand it completely, Can you confirm if my above statement is true ?

           

          Looking at below lines,

           

          SET Brand='';

          OdagBinding:

          LOAD * INLINE [

          VAL

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

          ];

          SET Brand_COLNAME='BrandName';

           

          1)Are you looking at Brand Column ?

          2)Should I list all the columns manually ?

          3)Is there a way to dynamically pick all columns from Selection/Shopping App