topic Re: qlikview script & select data without specifying the dates in QlikView

qlikview script & select data without specifying the dates

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi ,

how do I select data without specifying the dates in qlikview script?

For example today's date minus 6 months, that is select data for the last 6 months.

Re: qlikview script & select data without specifying the dates

chrisg — Mon, 25 Jul 2016 15:15:48 GMT

Hi,

do you have already a loadscript. Can you provide an example?

Manx thx

Christoph

Re: qlikview script & select data without specifying the dates

Anonymous — Mon, 25 Jul 2016 15:21:37 GMT

Hi,

Maybe you can use the function addmonths() addmonths ‒ QlikView

Using e.g. AddMonths(Today(),-6) will return 2016/01/25 (given that today is 2016/07/25). So, if you use this function in the where clause of your load script (e.g. Where Date >= AddMonths(Today(),-6)), it should return you all data for the last 6 months.

Good luck.

Re: qlikview script & select data without specifying the dates

Anonymous — Mon, 25 Jul 2016 16:09:45 GMT

thanks but I have an error:

WHERE	DATE>= (AddMonths(Today(),-6));

Re: qlikview script & select data without specifying the dates

Mon, 25 Jul 2016 16:26:00 GMT

Hi Sukai,

If following is my test data,

ReloadDate	ReloadBy
7/25/2016	A
6/1/2016	B
5/2/2016	C
4/3/2016	D
1/1/2016	E
12/10/2015	F
8/12/2015	G
5/7/2015	I

Please write the following script and Reload it,

LET vVar = Num(Date(Today()));

TestLoad:

LOAD * Where Num(ReloadDate)>=$(vVar1)-180;

LOAD ReloadDate,

ReloadBy

FROM

[..\Note Of Qlikview Learnings\Static_Data\Test Load.xlsx]

(ooxml, embedded labels, table is [Last 6Mnths data]);

Following records which fall under last 6 month period will be the output,

ReloadDate	ReloadBy
7/25/2016	A
6/1/2016	B
5/2/2016	C
4/3/2016	D

Thanks,

Narsi

Re: qlikview script & select data without specifying the dates

maxgro — Mon, 25 Jul 2016 16:41:54 GMT

AddMonths is a Qlik function

In the SQL part of the script you need database function (I think an IBM db2 from youir image)

In the Qlik part you can use Qlik functions (AddMonths)

It seems you used some Qlik functions (today, addmonths) in SQL, you can't

// Qlik

Load Date#(OrderDate,’YYYYMMDD’) as OrderDate,

AddMOnths.......,

............

;

// DB

SQL SELECT OrderDate

FROM … ;

Here for more detail

Preceding Load