topic Re: A function to check if a field value is correct date? in QlikView

A function to check if a field value is correct date?

iliyansomlev — Mon, 29 Jul 2013 11:47:48 GMT

Hi,

I have a string like 20030934 and another like 19871105 and another like 20030229. Obviously only the second is a correct date. Is there a function in qlikview like IsDate(20030934) to check if a value is correct date? What is the best way to do this check?

Best,

Iliyan

Re: A function to check if a field value is correct date?

pennetzdorfer — Mon, 29 Jul 2013 11:55:24 GMT

isnull(date(date#(20030933,'YYYYMMDD'),'YYYY-MM-DD'))

That returns 0 for valid an -1 for invalid dates.

Regards,

Florian

Re: A function to check if a field value is correct date?

kaushiknsolanki — Mon, 29 Jul 2013 11:55:54 GMT

Hi,

If the data coming from the source is in date format then qlikview will convert in date format and if the format is not recognized qlikview will consider that value as text.

But then you need to write the logic to identify which text should be considered as date.

Something like below.

If(num#(Mid(DATA,5,2))>12,'Not a Valid Month',

If(Num#(right(DATA,2)) > 31, 'Not a Valid Month')) as Flag.

Hope the logic is clear.

Regards,

Kaushik Solanki

Re: A function to check if a field value is correct date?

iliyansomlev — Mon, 29 Jul 2013 12:02:18 GMT

The data from the source is a string within which a date is coded in the first 8 symbols- 2013012312234. So I have to work with strings. I used the logic you mentioned, but the code becomes quite cumbersome because I have three cases in the coding logic of these strings (for dates before 1900, for XX century dates and for dates after 2000).

The idea to use IsNull as check seems short and intuitive, I will try and see how it works.

Thank you for your answers.

Best,

Iliyan

Re: A function to check if a field value is correct date?

Mon, 29 Jul 2013 12:02:28 GMT

Hi Iliyan,

please see the attached example for help.

Good luck!

Rainer

Re: A function to check if a field value is correct date?

iliyansomlev — Mon, 29 Jul 2013 12:12:58 GMT

Thanks, this gets closest to what I wanted, it's short and fast.

Re: A function to check if a field value is correct date?

mvaugusto — Thu, 11 Sep 2014 18:58:20 GMT

It's work for me

Re: A function to check if a field value is correct date?

Anonymous — Mon, 04 May 2015 11:36:17 GMT

I tried this and it looked good but BEWARE QlikView seems to have some serious date related bugs.

It allowed me to quite happily validate 29 February 2015 and even 31 February 2015. Only when I tried 32 February it it flag it up as 'Not Valid'.

Try this in a load script

[Date Check]:
LOAD mydate
, ALT(DATE#( mydate ,'DD-MMM-YYYY' ),'Not valid' ) AS [My Date]
INLINE
[mydate
28-FEB-2015
31-FEB-2015
32-FEB-2015
];

Re: A function to check if a field value is correct date?

Faizoel — Tue, 02 Feb 2016 08:24:36 GMT

And to make things even worse. 31-feb-2015 is shown as 03-mar-2015

Re: A function to check if a field value is correct date?

eniemenm — Fri, 30 Dec 2016 07:33:52 GMT

Makedate() function can handle whether or not the proposed date is actually a valid date. If not valid, it returns null. For example like this:

LOAD *,
makedate(mid(possible_date,1,4),mid(possible_date,5,2),mid(possible_date,7,2)) as isDate
inline [
possible_date
20150226
20150227
20150228
20150229
20150230
20150231
20150232

20160226
20160227
20160228
20160229
20160230
20160231
20160232
];

and the result looks like this:

Re: A function to check if a field value is correct date?

eniemenm — Fri, 30 Dec 2016 07:37:37 GMT

In the previous answers there seems to be problems with varying amount of days per month. Makedate() can handle those.