Date Condition

[bharatkishore]

Hi All,

@sunny_talwar

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

[sunny_talwar · Accepted solution]

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.

[bharatkishore]

Thank you Sunny Bhai for your reply. But i needed your suggestion .

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

[sunny_talwar]

1) 01.01.2011 is okay because it is already a month start, but do you have 21.01.2011? using Date(Mydate,'MM-YYYY') will show 01-2011, but the underlying will still be 21.01.2011. This is why I suggested MonthStart.... but if Mydate is always 1st of the month... then you don't need it.

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

[albert_guito]

Hi,

Use a date type in your where condition.

Try with 

... where Mydate >= Date(MakeDate(2017,1,1),'MM-YYYY');

Albert

[bharatkishore]

Thank you so much Sunny Bhai.. Thanks just last one point

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

[sunny_talwar]

It won't... unless you tell Qlik to read as date using Date#() function
where >= Date#('01-2017' , 'MM-YYYY')

[bharatkishore]

Thank you so much Sunny Bhai.. got it..