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: 
pranubitla
Contributor II
Contributor II

DateFormat Error

Dear experts,

we have list of users. want to show any user DOB entered as incorrectly want to mark them as error in Qlikview. I am taking data from excel spreadsheets loaded in to QV, would you please suggest something. see below sample data column 4& 5 error data.

    

ID DOB USER
113/02/1949Sara
216/12/1951Jeswanth
307/04/1946Stephen
4TempLinale
51O2Ram
612/05/1962Philip
721/07/1958David
801/05/1963merry
927/12/1950Timothy
1023/09/1941Jery
5 Replies
MK_QSL
MVP
MVP

Data:

Load *, If(Not IsNum(DOB),'Error') as DateError  Inline

[

  ID, DOB, USER

  1, 13/02/1949, Sara

  2, 16/12/1951, Jeswanth

  3, 07/04/1946, Stephen

  4, Temp, Linale

  5, 1O2, Ram

  6, 12/05/1962, Philip

  7, 21/07/1958, David

  8, 01/05/1963, merry

  9, 27/12/1950, Timothy

  10, 23/09/1941, Jery

]

its_anandrjs

Try this also will give extra column DOB_Error.

LOAD *,if( findoneof(DOB,'/')=0,'In Correct Date Format','Correct Date Format') as DOB_Error;

LOAD * Inline

[

ID, DOB, USER

1, 13/02/1949, Sara

2, 16/12/1951, Jeswanth

3, 07/04/1946, Stephen

4, Temp, Linale

5, 1O2, Ram

6, 12/05/1962, Philip

7, 21/07/1958, David

8, 01/05/1963, merry

9, 27/12/1950, Timothy

10, 23/09/1941, Jery

];

Output will be

DateFieldError.PNG

Regards

Anand

pranubitla
Contributor II
Contributor II
Author

Great, thanks for quick response

pranubitla
Contributor II
Contributor II
Author

Thanks Anand . Got the result

MK_QSL
MVP
MVP

Temp:

LOAD

  *,

  Alt(Date#(DOB,'DD/MM/YYYY'),'Not Valid Date' ) as DateFlag

INLINE

[

  ID, DOB, USER

  1, 13/02/1949, Sara

  2, 16/12/1951, Jeswanth

  3, 07/04/1946, Stephen

  4, Temp, Linale

  5, 1O2, Ram

  6, 12/05/1962, Philip

  7, 21/07/1958, David

  8, 01/05/1963, merry

  9, 27/12/1950, Timothy

  10, 23/09/1941, Jery

];