Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

How To: Create Null Values in Data

ChristineB
Alteryx Alumni (Retired)
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
Pliew
6 - Meteoroid

How to do this for in db?

rohashah
6 - Meteoroid

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

Joe_Mako
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
rohashah
6 - Meteoroid

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

Pliew
6 - Meteoroid

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

 

For In-DB, use NULL instead.

anotherusername
8 - Asteroid

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