Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
saivina2920
Creator
Creator

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

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

saivina2920_0-1613668632386.png

 

Labels (1)
2 Solutions

Accepted Solutions
edwin
Master
Master

try this as a calculated dimension:

edwin_0-1613691569147.png

 

View solution in original post

QFabian
Specialist III
Specialist III

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)

QFabian

View solution in original post

4 Replies
edwin
Master
Master

try this as a calculated dimension:

edwin_0-1613691569147.png

 

QFabian
Specialist III
Specialist III

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)

QFabian
Chanty4u
MVP
MVP

or you can do like below

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

or

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

Waïl_Riachi_Sanofi

Hello @Chanty4u ;

They tell me "missing right parenthesis"

Wal_Riachi_0-1632231112550.png