14,970
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→check if field value was not fulfilled: NULL, empty value
| Line 16: | Line 16: | ||
([http://sqlfiddle.com/#!2/9b01e/3/0 demo on sqlfiddle]) | ([http://sqlfiddle.com/#!2/9b01e/3/0 demo on sqlfiddle]) | ||
# {{kbd | key = NULL}} value: | # {{kbd | key = NULL}} value: | ||
#* solution: {{kbd | key = SELECT * FROM table_name WHERE column_name IS NULL;}} | #* MySQL solution: {{kbd | key = SELECT * FROM table_name WHERE column_name IS NULL;}} | ||
#* EXCEL: {{kbd | key =<nowiki>=EXACT(A2, "NULL")</nowiki>}} | #* EXCEL: {{kbd | key =<nowiki>=EXACT(A2, "NULL")</nowiki>}} | ||
# empty value: | # empty value: | ||
| Line 24: | Line 24: | ||
#* solution: step1: Replace the year > 100 from this year with empty value at EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)<1914, "", A2))</nowiki>}} (this formula also handle empty value and non well-formatted column value ex: 0000-12-31 ) ; step2: change the format of cell to time format | #* solution: step1: Replace the year > 100 from this year with empty value at EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)<1914, "", A2))</nowiki>}} (this formula also handle empty value and non well-formatted column value ex: 0000-12-31 ) ; step2: change the format of cell to time format | ||
#* trivial approach : EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)-YEAR(NOW())>100, "", A2))</nowiki>}} {{exclaim}} this formula could not handle empty value because it return 0. If I change the format of cell to time format, 0 will become 1900/1/0. | #* trivial approach : EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)-YEAR(NOW())>100, "", A2))</nowiki>}} {{exclaim}} this formula could not handle empty value because it return 0. If I change the format of cell to time format, 0 will become 1900/1/0. | ||
# Using PHP [http://php.net/manual/en/function.empty.php empty()] function to find 0, null, false, empty string, empty array values. | |||
== check if field contains value == | == check if field contains value == | ||