
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Date Condition
Hi All,
I have a Date field and the format is 01.01.2011. Now i am converting the date format in script as
Date(Mydate, 'MM-YYYY') as Period.
Now in data in have data from 2011 and i need only from 2017.
For that i am giving condition as
Load *,
From qvd where Mydate>= '01-2017'
But for some how it is not working.
Can you please tell me where i am missing..
Thanks,
Bharat
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
First thing... make sure to use MonthStart when you just display MM-YYYY so that the date is converted to the 1st day of the month
Date(MonthStart(Mydate), 'MM-YYYY') as Period
Next try this
Load * From qvd Where Period >= MakeDate(2017);
Although, this isn't the best way to pull data from QVD, because this isn't going to give you an optimized load... To get optimized load... try something like this
LOAD Monthstart(MakeDate(2017), IterNo() - 1) as Period AutoGenerate 1 While MakeDate(2017), IterNo() - 1 <= MonthEnd(Today()); LOAD * From qvd Where Exsits(Period);
This will not give you an optimized load.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
First thing... make sure to use MonthStart when you just display MM-YYYY so that the date is converted to the 1st day of the month
Date(MonthStart(Mydate), 'MM-YYYY') as Period
Next try this
Load * From qvd Where Period >= MakeDate(2017);
Although, this isn't the best way to pull data from QVD, because this isn't going to give you an optimized load... To get optimized load... try something like this
LOAD Monthstart(MakeDate(2017), IterNo() - 1) as Period AutoGenerate 1 While MakeDate(2017), IterNo() - 1 <= MonthEnd(Today()); LOAD * From qvd Where Exsits(Period);
This will not give you an optimized load.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
1) When i have date in this format 01.01.2011. What if i just convert to
Date(Mydate,'MM-YYYY') and why do i need to use Monthstart.
2) Normally when we have date from 2011 and if i need only from 2017 sometimes when we give where Date>=2017 it works. But now if use Makedate in where condition then only it works. Can you please tell me why?
Load *
From qvd
Where Period >= MakeDate(2017);
Thanks,
Bharat

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
2) QlikView/Qlik Sense is smart enough to interpret some formats as date (based on your environmental variable... but this Mydate>= '01-2017' is not read as date by Qlik... most likely read as a text... which is why the comparison didn't work. MakeDate() create a date for you

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Use a date type in your where condition.
Try with
... where Mydate >= Date(MakeDate(2017,1,1),'MM-YYYY');
Albert

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
As you mentioned in point 1 mydate is always 1st of the month..When i convert it to some format i.e. MM-YYYY and give where >= '01-2017' will it work or as you suggested do i need to use Where Period >= MakeDate(2017);

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
where >= Date#('01-2017' , 'MM-YYYY')

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you so much Sunny Bhai.. got it..
