topic Script where clause in QlikView

Script where clause

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

i have a date field like 2015-07-28 11:00:00.000

I want to load the date 2015-07-28 11:00:00.000 to 2015-06-28 11:00:00.000

how to achieve this in where clause?

thanks In advance

Re: Script where clause

qlikviewwizard — Mon, 18 Jul 2016 06:32:13 GMT

Can you explain more

Re: Script where clause

jonathandienst — Mon, 18 Jul 2016 06:37:04 GMT

It depends on your data source. Are you loading from SQL, QVD, Excel, txt? And how do you determine the date load range?

As always, you will get more accurate and more specific help if you upload a small sample qvw. It does not need to contain any sensitive data.

Re: Script where clause

stephenedberkg — Mon, 18 Jul 2016 06:38:33 GMT

i have a date field

id name date

1 aaa 2015-07-28 11:00:00.000

2 bbb 2015-08-28 11:00:00.000

3 ccc 2015-09-28 11:00:00.000

4 ddd 2015-10-28 11:00:00.000

5 eee 2015-11-28 11:00:00.000

6 fff 2015-12-28 11:00:00.000

7 ggg 2016-01-28 11:00:00.000

8 hhhh 2016-02-28 11:00:00.000

9 iiii 2016-03-28 11:00:00.000

10 kkk 2016-04-28 11:00:00.000

11 lll 2016-05-28 11:00:00.000

12 mmm 2016-06-28 11:00:00.000

13 nnn 2016-07-28 11:00:00.000

14 ooo 2016-08-28 11:00:00.000

in this table i want to load only the 2015-08-28 11:00:00.000 date to 2016-06-28 11:00:00.000 date

in where condition how can i achieve this?

Re: Script where clause

Mon, 18 Jul 2016 06:39:29 GMT

You should just be able to do something along the lines of:

LOAD

[FIELDS]

FROM

[Source]

WHERE

[Date Field]>= DATE#('28/06/2015', 'DD/MM/YYYY')

AND

[Date Field]<= DATE#('28/07/2015', 'DD/MM/YYYY')

Re: Script where clause

PrashantSangle — Mon, 18 Jul 2016 06:41:56 GMT

Hi,

Where date>='2015-06-28 11:00:00.000' and date<='2015-07-28 11:00:00.000';

Note : to work above expression you must have date and comparing field in same format

Regards

Re: Script where clause

stephenedberkg — Mon, 18 Jul 2016 06:42:54 GMT

PFA of QVW file

Re: Script where clause

Ponkaviyarasu — Mon, 18 Jul 2016 06:44:21 GMT

Hi Stephen,

I'm also new to Qlik view, My suggestion is you can try AddMonths('2015-07-28',-1) in case of particular data.

Re: Script where clause

Mon, 18 Jul 2016 06:45:24 GMT

tablea:

load * inline

[

id , name , date

1 , aaa , 2015-07-28 11:00:00.000

2 , bbb , 2015-08-28 11:00:00.000

3 , ccc , 2015-09-28 11:00:00.000

4 , ddd , 2015-10-28 11:00:00.000

5 , eee , 2015-11-28 11:00:00.000

6 , fff , 2015-12-28 11:00:00.000

7 , ggg , 2016-01-28 11:00:00.000

8 , hhhh , 2016-02-28 11:00:00.000

9 , iiii , 2016-03-28 11:00:00.000

10 , kkk , 2016-04-28 11:00:00.000

11 , lll , 2016-05-28 11:00:00.000

12 , mmm ,2016-06-28 11:00:00.000

13 , nnn , 2016-07-28 11:00:00.000

14 , ooo , 2016-08-28 11:00:00.000

];

tableb:

NoConcatenate

load *

resident tablea

where date(date,'YYYY-MM-DD hh:mm:ss.fff') > date('2015-08-28 11:00:00.000','YYYY-MM-DD hh:mm:ss.fff') and date(date,'YYYY-MM-DD hh:mm:ss.fff') <=date('2016-06-28 11:00:00.00','YYYY-MM-DD hh:mm:ss.fff');

drop table tablea;

Re: Script where clause

karthikoffi27se — Mon, 18 Jul 2016 06:46:43 GMT

Hi Stephen

Try this please

Test1:

Load *

From sample qvd.(qvd)

Where

trim(date(date(DATE,'DD/MM/YYYY hh:mm:ss[.fff] TT'),'DD/MM/YYYY') >= 2015-07-28 AND trim(date(date(DATE,'DD/MM/YYYY hh:mm:ss[.fff] TT'),'DD/MM/YYYY') <= 2015-07-28

Many Thanks

Karthik

Re: Script where clause

jonathandienst — Mon, 18 Jul 2016 07:10:55 GMT

Use this where clause:

Where DATE >= Date#('2015-07-28 11:00:00', 'yyyy-MM-dd hh:mm:ss') And DATE <= Date#('2015-06-28 11:00:00.000', 'yyyy-MM-dd hh:mm:ss')

But note that if the DATE does not contain time values (as in your sample), then this will not load data for 28 July 2015 and the first date will be 29 July.