How To: Create Null Values in Data

[ChristineB]

How To: Create Null Values in Data

In processing and formatting your data, you may decide that some records should be classified as Null values. Rather than fill in numeric fields with a 0 or leave a field empty (‘’), a Null value may be the best option for analyzing and storing your data.

Procedure

1. In this particular example, let’s pretend that we have customer IDs and the phone numbers associated with them.  Some phone numbers may be incomplete or mistyped. 
   
   
2. After removing punctuation with the Data Cleansing Tool, we’ll look for valid phone numbers – those composed of 10 digits – to store in our Customer database. 
   
   
3. To identify our valid results, we’ll apply a conditional statement:  IF a phone number is not 10 digits long, THEN the record is Null; otherwise (ELSE) use the phone number in our data.  
   
   1. The expression used for assigning a Null value can be found in the Formula Tool's specialized functions:
      
      
      
      
      
4.  As a result of the above expression, both the phone numbers that are either one digit too short (9 digits in length) or a digit too long (11 digits in length) have been cast as Null: 
   
   
   
   

[Pliew]

How to do this for in db?

[rohashah]

is it possible to convert every empty cell in my database to [null]?

[Joe_Mako]

@Pliew does the Formula In-DB tool not allow you to do this?

@rohashah have you tried the Multi-Field Formula tool? It allows you to select all fields, and then you can use an expression like:

IF IsEmpty([_CurrentField_]) THEN Null() ELSE [_CurrentField_] ENDIF

[rohashah]

Thanks, Joe! I was using IF ([CurrentField_])="" THEN Null(), which I guess is different than "IsEmpty". Thanks again!

[Pliew]

NULL() did not work in in-DB formula.

For In-DB, use NULL instead.

[anotherusername]

In-DB uses whatever syntax the DB itself requires (because it just sends SQL to the DB server to execute) - so you'd need to look up the help pages to find an ISNULL-equivalent function for TSQL for MS SQL Server, IBM for Netezza, etc depending on what you're working in.

(P.S. Thanks for the multi-field formula!  I spend most of my time using in-DB, so need to use the forum to learn the Alteryx syntax and tools!)