Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

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
3 Replies
Highlighted
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Partner
Partner

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

 

Highlighted
Partner
Partner

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

Plees ekskuse my Swenglish and or Norweglish spelling misstakes