Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

shahamel
Contributor

declare and variable

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';
Tags (3)
1 Solution

Accepted Solutions

Re: declare and variable

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

4 Replies

Re: declare and variable

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

shahamel
Contributor

declare and variable

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

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

shahamel
Contributor

declare and variable

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.