topic Re: How to consider null date if date is '01/01/0001' in QlikView

How to consider null date if date is '01/01/0001'

saivina2920 — Thu, 18 Feb 2021 17:18:02 GMT

How to consider null date if date is '01/01/0001'

Date(Floor([EMP_RELIEVE_DATE1]), 'DD/MM/YYYY') as [EMP_RELIEVE_DATE],
if(len([EMP_RELIEVE_DATE1])=0,1,0) as HasNullDate
Inline [
DOC_NO,EMP_STATUS,EMP_COUNTRY,EMP_STATE,EMP_RESIGN_DATE1,EMP_RELIEVE_DATE1
DOC-001,Current,Australia,Sydney,14/08/2020,
DOC-001,Current,Australia,Melbourne,15/08/2020,15/09/2020
DOC-002,Current,India,Gujarat,20/08/2020,20/09/2020
DOC-003,Current,India,Gujarat,25/08/2020,01/01/0001
DOC-004,Current,India,Gujarat,12/01/2021,02/02/2021

Re: How to consider null date if date is '01/01/0001'

edwin — Thu, 18 Feb 2021 23:39:41 GMT

try this as a calculated dimension:

Re: How to consider null date if date is '01/01/0001'

QFabian — Thu, 18 Feb 2021 23:51:12 GMT

Hi @saivina2920 please try this , because valid date stars in 1/1/1900 with num() value = 1, so if the date hasnt correct vlue, is going to show a negative result:

num('01/01/0001') = -693593

=if(num(EMP_RELIEVE_DATE1)>0,0,1)

Re: How to consider null date if date is '01/01/0001'

Chanty4u — Fri, 19 Feb 2021 06:40:08 GMT

or you can do like below

if(Yourdate='01/01/0001','No date found', Yourdate) as Nulldate

if(not isNum(Yourdate),'Nulldate',date(yourdate,'DD/MM/YYYY')) as Date

Re: How to consider null date if date is '01/01/0001'

Waïl_Riachi_Sanofi — Tue, 21 Sep 2021 13:32:28 GMT

Hello @Chanty4u ;

They tell me "missing right parenthesis"