Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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 |
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
]
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
Regards
Anand
Great, thanks for quick response
Thanks Anand . Got the result
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
];