4 Replies Latest reply: Feb 14, 2012 3:54 AM by Sandeep Hamelink RSS

    declare and variable

    Sandeep Hamelink
      Dear users,
      I am using Qlikivew 11 and came across a problem. I am having trouble with "Declare" and Subset. How can i declare both time variables as well as the variable subset. I want to use subset as a variable which reacts while making a selection at the front end.
      Many thanks in advance
      Sandeep
      //DECLARE
      // @FROMDATE DATETIME,
      // @TODATE   DATETIME
      LOAD *;
      SQL
      SELECT
      EKKO.EKORG AS PURCHASE_ORGANISATION,
      EKKO.EKGRP AS PURCHASE_GROUP,
      EKKO.EBELN AS PURCHASE_ORDER,
      EKKO.BSART AS DOCUMENT_TYPE,
      BS.DESCRIPTION AS DOCUMENT_TYPE_DESCRIPTION,
      EKKO.AEDAT AS CREATION_DATE,
      EKKO.ERNAM AS CREATOR,
      SEL.UDATE AS CHANGE_DATE,
      SEL.USERNAME AS UPDATE_USER,
      TNAME.DESCRIPTION AS TABLE_NAME,
      FNAME.DESCRIPTION AS FIELD_NAME,
      RIGHT(CDPOS.TABKEY,6) AS TABLE_KEY,
      C.DESCRIPTION AS CHANGE_TYPE,
      SEL.TCODE AS TRANSACTION_CODE,
      CDPOS.UNIT_OLD AS OLD_UNIT,
      CDPOS.UNIT_NEW AS NEW_UNIT,
      CDPOS.CUKY_OLD AS OLD_CURRENCY,
      CDPOS.CUKY_NEW AS NEW_CURRENCY,
      LTRIM(CDPOS.VALUE_OLD) AS OLD_VALUE,
      LTRIM(CDPOS.VALUE_NEW) AS NEW_VALUE,
      EKKO.LOEKZ AS DELETED,
      CASE WHEN DATEADD(MM, -1, SEL.UDATE) < EKKO.AEDAT THEN 'WITHIN 1 MONTH' ELSE 'OVER ONE MONTH' END AS CHANGE_DELAY,
      1 AS DOCUMENT_COUNT,
      20 as INDICATORID,
      25 as SCORECARDID
      FROM
      "name database".dbo."SDC_CDHDR" SEL INNER JOIN
      "name database".dbo."SDC_EKKO" EKKO ON SEL.COMPANYCODE = EKKO.COMPANYCODE AND
      EKKO.EBELN = SEL.OBJECTID AND SEL.OBJECTCLAS = 'EINKBELEG' INNER JOIN
      "name database".dbo."SDC_CDPOS" CDPOS ON SEL.COMPANYCODE = CDPOS.COMPANYCODE AND SEL.OBJECTCLAS = CDPOS.OBJECTCLAS AND
      SEL.OBJECTID = CDPOS.OBJECTID AND SEL.CHANGENR = CDPOS.CHANGENR  LEFT JOIN
      "name database".dbo."SDC_CUST" TNAME ON SEL.COMPANYCODE = TNAME.COMPANYCODE AND TNAME.CUSTOMTYPE = 'TABNAME' AND TNAME.CUSTOMKEY = CDPOS.TABNAME LEFT JOIN
      "name database".dbo."SDC_CUST" FNAME ON SEL.COMPANYCODE = FNAME.COMPANYCODE AND FNAME.CUSTOMTYPE = 'ROLLNAME' AND FNAME.CUSTOMKEY = CDPOS.FNAME LEFT JOIN
      "name database".dbo."SDC_CUST" BS ON EKKO.COMPANYCODE = BS.COMPANYCODE AND BS.CUSTOMTYPE = 'BSART' AND BS.CUSTOMKEY = EKKO.BSART LEFT JOIN
      "name database".dbo."SDC_CHANGETYPE" C ON CDPOS.CHNGIND = C.CHNGIND
      EITID
      WHERE
      // // AND
      // // #SUBSET# AND
      // // @FROMDATE <= Periode AND Periode < @TODATE
      EKKO.BSTYP = 'F' AND
      SEL.CHANGE_IND = 'U';
        • Re: declare and variable
          Miguel Angel Baeyens de Arce

          Hi,

           

          The following should work:

           

          LET vFromDate = Date('09/02/2012', 'YYYYMMDD'); // This will return 20120209, you can change to fit your needs
          LET vToDate = Date(YearEnd(Today()), 'YYYYMMDD'); // This will return 20121231
          
          Table:
          LOAD *;
          SQL SELECT *
          FROM Table
          WHERE Period >= '$(vFromDate)' AND Period < '$(vToDate)'
          

           

          Hope that helps.

           

          Miguel

            • declare and variable
              Sandeep Hamelink

              Thanks, it worked  : This is my current script: It is much more simpified, but it works. I have only one question. It is possible to make vtFromDate and vToDate changeable. For example by using an inputfield. In the meantme i will try myself.

               

              SET ThousandSep ='.';
              SET DecimalSep=',';
              SET MoneyThousandSep='.';
              SET MoneyDecimalSep=',';
              SET MoneyFormat='€ #.##0,00;€ -#.##0,00';
              SET TimeFormat='h:mm:ss';
              SET DateFormat='D-M-YYYY';
              SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';
              SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';
              SET DayNames='ma;di;wo;do;vr;za;zo';
              LET vFromDate = Date('01/01/2012', 'YYYYMMDD'); // This will return 20120101, you can change to fit your needs
              LET vToDate = Date(YearEnd(Today()), 'YYYYMMDD'); // This will return 20121231
              //SET svFromDate='vFromDate';
              //SET svToDate='vToDate';

              ODBC CONNECT32 TO [SUDBPROD SQL2008];


              //DECLARE
              // @FROMDATE DATETIME,
              // @TODATE   DATETIME

              //SELECT
              // @FROMDATE = :FROMDATE,
              // @TODATE   = :TODATE


              LOAD *;
              SQL
              SELECT
              BoekingId,
              D.Kostenplaats,
              Bedrag,
              Periode,
              GROOTBOEK
              FROM          SDC_BOEKINGEN               D
              WHERE
              D.GROOTBOEK = 8080 AND
              // // #SUBSET# AND
              // // @FROMDATE <= Periode AND Periode < @TODATE
              '$(vFromDate)'  <=  Periode AND Periode < '$(vToDate)'

               

               

               

                • Re: declare and variable
                  Miguel Angel Baeyens de Arce

                  Hi,

                   

                  The answer is yes if and only if you are going to perform attended reloads (meaning that this file is not going to be reloaded at a given time by a server) using the following code:

                   

                  LET vFromDate = Input('Please specify date in format YYYYMMDD', 'Warning!');
                  

                   

                  That will show a dialog at the time the script reach that line prompting the user and stopping the reload until some value is entered by the user.

                   

                  Hope that helps.

                   

                  Miguel