Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
stephenedberkg
Creator II
Creator II

Script where clause

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

10 Replies
qlikviewwizard
Master II
Master II

Can you explain more

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
stephenedberkg
Creator II
Creator II
Author

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?

Not applicable

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')

PrashantSangle

Hi,

like

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
stephenedberkg
Creator II
Creator II
Author

PFA of QVW file

Ponkaviyarasu
Partner - Contributor III
Partner - Contributor III

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.


Not applicable

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;

karthikoffi27se
Creator III
Creator III

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