Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

How To: Create Null Values in Data

Alteryx
Alteryx
Created

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:

      2019-02-28_8-57-58.png


  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: 

    2019-02-28_9-01-03.png

Attachments
Comments
6 - Meteoroid

How to do this for in db?

6 - Meteoroid

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

12 - Quasar
12 - Quasar

@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
6 - Meteoroid

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

6 - Meteoroid

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

 

For In-DB, use NULL instead.

Alteryx Certified Partner

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!)