0 Replies Latest reply: Oct 11, 2017 7:24 PM by Karthikeyan Subramaniam 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);