topic Re: String to Date in QlikView

String to Date

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

Hi All,

I have date in 'MMM DD YYYY' format.

I am trying to get the data after 8/8/2015. So I am trying to convert the string into Date.

I am trying something like this:

=if(Date(Date#([As of Date],'MMM DD YYYY'),'MM/DD/YYYY')>='08/08/2015', 'Yes', 'No')

This is working only if, Month is greater than 'August'. If I select Jan 10 2016, it is dispalying the output as 'No'. I want to display it as 'Yes', as it is greater than 8/8/2015

Thanks in advance.

Best Regards,

Varun Reddy

Re: String to Date

sunny_talwar — Tue, 08 Mar 2016 21:47:12 GMT

Can you try this:

=If(Num(Date#([As of Date], 'MMM DD YYYY')) >= Num(MakeDate(2015, 8, 8)), 'Yes', 'No')

UPDATE: Tried a sample and it seems to work:

Table:

LOAD *,

If(Num(Date#([As of Date], 'MMM DD YYYY')) >= Num(MakeDate(2015, 8, 8)), 'Yes', 'No') as Flag;

LOAD * Inline [

As of Date

Jan 10 2016

];

Re: String to Date

varunreddy — Tue, 08 Mar 2016 21:50:38 GMT

Hi Sunny,

Why are we using Num?

Re: String to Date

maxgro — Tue, 08 Mar 2016 21:50:40 GMT

=if(Date#([As of Date], 'MMM DD YYYY')>=MakeDate(2015,8,8), 'Yes', 'No')

Re: String to Date

varunreddy — Tue, 08 Mar 2016 21:52:20 GMT

Hi Maxgro,

Could you please explain we using Makedate?

Re: String to Date

sunny_talwar — Tue, 08 Mar 2016 21:52:28 GMT

You don't have to use it, but I like to do a date comparison in terms of numbers rather than in terms of date.

Re: String to Date

sunny_talwar — Tue, 08 Mar 2016 21:53:41 GMT

Some information on MakeDate() -> makedate ‒ QlikView

Generally, it is used to create a date

Re: String to Date

varunreddy — Tue, 08 Mar 2016 21:54:09 GMT

Sunny,

Thanks for the Quick response. Could you please explain the reason why are we using make date?

Regards,

Varun

Re: String to Date

varunreddy — Tue, 08 Mar 2016 21:57:01 GMT

Thanks Sunny

Re: String to Date

rubenmarin — Tue, 08 Mar 2016 22:00:16 GMT

Seems it's working for me just like you said, maybe is something different? or I didn't get where was the problem?

edit:lot of conversation while I was testing, I should remember to refresh the page

Re: String to Date

sunny_talwar — Tue, 08 Mar 2016 23:05:23 GMT

I am not sure why '08/08/2015' worked for some dates and not for other dates, but the reason I find MakeDate to be more reliable then date within single quote is because of the following 2 reasons

1) QlikView won't interpret it as date for any number of reasons. One could be the date format supplied doesn't match your SET DateFormat at the beginning.

2) 08/08/2015 is easy to interpret as 8th August, but 08/07 can be Aug 7th or July 8th. Making the distinction can be difficult if you just have date. With MakeDate() function you are sure what is what. MakeDate take the following arguments -> MakeDate(Year, Month, Date). So you are 100% sure of your inputs.

HTH

Best,

Sunny

Re: String to Date

swuehl — Tue, 08 Mar 2016 23:25:45 GMT

Varun Reddy,

it's indeed kind of strange what you observed.

Are you sure you made selections in [As of Date] and not in another calendar field, so that [As of Date] might show multiple possible values for your specific selection (just asking because an as-of-table will usually have two fields with similar values)?

In this case,

=if(Date(Date#([As of Date],'MMM DD YYYY'),'MM/DD/YYYY')>='08/08/2015', 'Yes', 'No')

will return 'No', because the implicite Only( [As of Date] ) will return NULL, hence the comparison fails.