Nulls Handling - String, Date, Numerical Data types

Version 8

    This document is prepared intend to handle the nulls.

     

    We may have nulls in String Data type Columns, Date Data Type Columns, Numeric Data Type Columns, Which cracks our heads while creating calculations, This document will help us to overcome from that. We may have few more methods apart from this document but i used the following techniques in my experience.

    This document is not referencing Geographical Data.

     

    The following Excel is taking as Data set to create example of Handling Nulls.

    Picture1.png

     

    1) Nulls Handling - Numerical Data:


    While doing calculations, We try to convert the nulls to 0 (zero) to get the solutions.

    We can handle the Numerical Nulls by using the following different ways.

    1) ISNULL

    2) IFNULL

    3) ZN

    4) IIF

     

    1)  ISNULL:

    This function tests  the Numerical columns then gives output as “True” or “False”. By using true and False values, we can define our desired values as output.

    EX: In the following Example, If Sales value is null then replaced by 0, If Sales value is not null then showing Sales as it is.

    2.png

    The following image illustrates 2 Customers ‘Sales Values’ having Nulls.

    Picture2.pngPicture3.png

    The following image is showing the [Sales Values] data after nulls are replaced by 0 (Zero).

    Picture4.pngPicture5.png

     

     

    2) IFNULL:

    This function test the data, if value is null that null value is replaced by our desired value ( in this example null replaced by 0 (Zero)).

    Picture6.png

     

    The following image is showing the [Sales Values] data after nulls are replaced by 0 (Zero).

    Picture7.pngPicture8.png


    3) ZN:

    This function tests the Numerical data, then nulls will be replaced by 0(ZERO) by Tableau Software itself. Here We can’t replace nulls by our desired Value.

    Picture9.pngPicture10.png

     

    4) IIF:

    This Function test the data, If it finds null, we can we replace Nulls by our desired Value.

    (iif( isnull([Order Values]), 0,[Order Values]))

    Picture11.png

    The following images are showing the [Sales Values] data after nulls are replaced by 0 (Zero)

     

    Picture12.pngPicture13.png

     

    2) Nulls Handling - String Data:


    We can handle the nulls in String Columns using the following Functions.

    1) ISNULL

    2) IFNULL

    3) IIF


    1) ISNULL

    This function tests the Data, if it find nulls those will be replaced by out desired string value.Picture14.png

    The following image has Customers Names, in that first record has no Name it means it is Null.

    Picture15.png

    The following Image showing that String Null is replaced by our custom string “This Record is Null”. Here we can place our desired String data instead of “This Record is Null”.

    Picture16.png

     

     

    2) IFNULL:

    This Function Tests the String Data, if it finds Null data those will be replaced by our desired String Value.

    Picture17.png


    The following Image is showing that String Null is replaced by our custom string “This Record is Null”. Here we can place our desired String data instead of “This Record is Null”.

    Picture18.png


    3) IIF:

    This Function Tests the String Data, if it finds Null data those will be replaced by our desired String Value.

    Picture19.png

    The following Image is showing that String Null is replaced by our custom string “This Record is Null”. Here we can place our desired String data instead of “This Record is Null”. Picture20.png

     

    3) Nulls Handling - Date Data:


    Generally, In Measure Columns, We replaced the Null Values by zero , in String  columns by Desired String, Similarly We replace Nulls in Date Field by any Date.

    In this Document Null Dates replaced by 31-01-1200

    We can handle the Nulls in Date Data type  fields by using the following functions

    1) ISNULL

    2) IFNULL

    3) IIF

     

    1) ISNULL:

    This Function Tests the Date Data, if it finds Null data those will be replaced by our desired Date.

    Picture1.png

     

    The Following Image is Showing Nulls in Date Data Type Column , Those nulls are replaced by  Date, 31-12-1200.Picture2.png

     

    2) IFNULL

    This Function Tests the Date Data, if it finds Null data those will be replaced by our desired Date.Picture3.png

     

    The Following Image is Showing Nulls in Date Data Type Column , Those nulls are replaced by  Date, 31-12-1200.

    Picture6.png


    3) IIF


    This Function Tests the Date Data, if it finds Null data those will be replaced by our desired Date.

     

    The Following Image is Showing Nulls in Date Data Type Column , Those nulls are replaced by  Date, 31-12-1200Picture1.pngPicture2.png

     


    Note: We may have few more approaches to handles the nulls apart from the above methods.


     


    Thanks & Regards

    Laxman Kumar