topic declare and variable in QlikView

declare and variable

shahamel — Thu, 09 Feb 2012 12:29:16 GMT

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 — Thu, 09 Feb 2012 12:47:48 GMT

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

shahamel — Thu, 09 Feb 2012 13:46:49 GMT

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 — Thu, 09 Feb 2012 13:55:35 GMT

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

declare and variable

shahamel — Tue, 14 Feb 2012 08:54:49 GMT

With your previous answer i did not get the expected result, but you did gave the right answer through another answer to another question from someone else. I used your application dates-set-analysis-basics.qvw to get the expected result.