Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Can you explain more
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.
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?
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')
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
PFA of QVW file
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.
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;
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