EmptyIsNull Script and chart function

Overview

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • CustomerID

  • OrderID

  • Product

  • Status

  • PhoneNumber

Load script

Example:
LOAD * inline [
CustomerID, OrderID, Product, Status, PhoneNumber
1, 1001, Widget,, (0123) 456 789
2, 1002, Gizmo, Open,-
3, 1003, Gadget, Closed,()
4, 1004, Widget,,678-9888
];

Results

Load the data and open a sheet. Create a new table and add these fields as dimensions:

• CustomerID

• OrderID

• Product

• Status

Create the following calculated dimensions:

• =EmptyIsNull(Status), to return as NULL any empty string values in the Status field.

• =EmptyIsNull(PurgeChar(PhoneNumber, ' -()')), to remove any dashes, spaces, and parentheses from the PhoneNumber field. If there are no characters left, the EmptyIsNull function returns the empty string as NULL; an empty phone number is the same as no phone number.

In the Status column, notice that the records with empty values appear as empty cells with a default transparent background color. The output of the EmptyIsNull(Status) measure returns NULL for these empty values, as indicated by the dash (-) character with gray cell background.

The second measure uses the EmptyIsNull function with the PurgeChar Script and chart function to remove any dashes (-), spaces, and parentheses () from the PhoneNumber field, and then return any remaining empty strings as NULL. The cells with null values now appear with a dash (-) and gray background formatting.

Overview

A sales manager wants to analyze sales data and easily identify the records that contain missing values.

Open the Data load editor and add the load script below to a new section.

The load script contains:

• A dataset which is loaded into a data table called Example.

• The following fields in the data table:

  • ID

  • Name

  • Age

  • Email

  • Sales

  • Region

  • OrderStatus

Load script

Example:
LOAD * inline [
ID,Name, Age, Email, Sales, Region, OrderStatus
1, John Smith, 30, john@email.com, 1000, North, Active
2, Jane Doe, '', jane@email.com, 1500, South, Active
3, Bob Johnson, 45, '', 800, East, Inactive
4, Alice Brown, 28, alice@email.com, '', West, Active
5, Charlie Lee, '', charlie@email.com, 1200, '', Active
6, Eva Green, 35, eva@email.com, 950, North, ''
7, David White, 50, '', '', South, Inactive
8, Fiona Black, 42, fiona@email.com, 1100, East, Active
];

Results

Load the data and open a sheet. Create a new table and add this field as a dimension:

• ID

Create the following calculated dimensions:

• =If(IsNull(EmptyIsNull(Age)), 'NULL', EmptyIsNull(Age)), to return as NULL any empty string values in the Age field, and then identify and populate all null values with the value NULL instead of a dash (-) character.

• =If(IsNull(EmptyIsNull(Email)), 'NULL', EmptyIsNull(Email)), to return as NULL any empty string values in the Email field, and then identify and populate all null values with the value NULL instead of a dash (-) character.

• =If(IsNull(EmptyIsNull(Sales)), 'NULL', EmptyIsNull(Sales)), to return as NULL any empty string values in the Sales field, and then identify and populate all null values with the value NULL instead of a dash (-) character.

• =If(IsNull(EmptyIsNull(Region)), 'NULL', EmptyIsNull(Region)), to return as NULL any empty string values in the Region field, and then identify and populate all null values with the value NULL instead of a dash (-) character.

• =If(IsNull(EmptyIsNull(OrderStatus)), 'NULL', EmptyIsNull(OrderStatus)), to return as NULL any empty string values in the OrderStatus field, and then identify and populate all null values with the value NULL instead of a dash (-) character.

Set conditional cell formatting to change the background color to red for NULL values and green for all other values. In the Properties panel for each dimension and measure, enter the following expressions as the Background color expression:

• ID (dimension): =RGB(200,255,200)

• Age (measure): =If(IsNull(EmptyIsNull(Age)), RGB(255,200,200), RGB(200,255,200))

• Email (measure): =If(IsNull(EmptyIsNull(Email)), RGB(255,200,200), RGB(200,255,200))

• Sales (measure): =If(IsNull(EmptyIsNull(Sales)), RGB(255,200,200), RGB(200,255,200))

• Region (measure): =If(IsNull(EmptyIsNull(Region)), RGB(255,200,200), RGB(200,255,200))

• OrderStatus (measure): =If(IsNull(EmptyIsNull(OrderStatus)), RGB(255,200,200), RGB(200,255,200))

The results show that by using the EmptyIsNull function and adding conditional formatting, you can easily identify records with missing values.