Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
thanhng34
Partner - Contributor III
Partner - Contributor III

Multiple conditions to identify errors on a Date field

 
Hi everyone,
 
I want to create a flag (FlagErrorDate) to identify errors in the "Date of birth" field. The rule is as follows:

If Date (Date of birth) is not between '01' and '31'
or Month (Date of birth) is not between '01' and '12'
or Year (Date of birth) <= 1900 -> 'Error', otherwise 'OK'

So I wrote the following expression but it didn't work :

If (not match (Date (Date of birth), '01', '02', '03', ....., '31)) or not match (Mont (Date of birth), '01', ' 02 ',' 03 ', .....,' 12 ') or Year (Date of birth) <= 1900,' Error ',' OK 'as FlagErrorDate

Can someone help me write the right expression? Is there another method of doing this without having to list all the values to compare: 01, 02, 03, .... 31

Thank you very much in advance
Labels (3)
3 Replies
Vegar
MVP
MVP

I think you are missing brackets around Date of birth. Like below.

If (not match (Date ([Date of birth] ), '01', '02', '03', ....., '31)) or not match (Mont ([Date of birth]), '01', ' 02 ',' 03 ', .....,' 12 ') or Year ([Date of birth]) <= 1900,' Error ',' OK 'as FlagErrorDate

thanhng34
Partner - Contributor III
Partner - Contributor III
Author

Hi Vegar,

This expression didn't work. I have 'Error' on all cells of the new field "FlagErrorDate".

This is what I wrote :

if(not match (Date("Date of birth"),'01','02','03','04','05','06','07','08','09','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31') or not match(Month("Date of birth"), '01','02','03','04','05','06','07','08','09','10','11','12')
or year("Date of birth")<=1900, 'Error', 'OK') as FlagErrorDate

 

Vegar
MVP
MVP

Oh I think I see what's causing this issue 🙂  Date("Date of birth") will return a date not the day of month. Try to use  Day("Date of birth") instead.

if(not match (Day("Date of Birth"), '1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31') or not match(Month("Date of birth"), '01','02','03','04','05','06','07','08','09','10','11','12')
or year("Date of birth")<=1900, 'Error', 'OK') as FlagErrorDate