Null Operators

Use is null and is not null to check whether a value is or is not null. is null returns True when a value is null. is not null returns True when a value is not null.

This example returns rows that contain Sub_Category fields that are not null and the counts of rows that contain each field.

1q = load "Superstore";
2q = filter q by 'Sub_Category' is not null;
3q = group q by 'Sub_Category';
4q = foreach q generate 'Sub_Category' as 'Sub_Category', count() as 'count';
5q = limit q 2000;

Sub-Category	Count of Rows
Accessories	775
Appliances	466
Art	796
Binders	1,523
Bookcases	228
Chairs	617
Copiers	68
Envelopes	254
Fasteners	217
Furnishings	957
Labels	364
Machines	115
Paper	1,370
Phones	889
Storage	846
Supplies	190
Tables	319

Replace Null Values with case

Use case to replace null values with a value of your choice. This example labels the null Sub-Category field "Empty."

1q = load "Superstore";
2q = group q by 'Sub_Category';
3q = foreach q generate case when 'Sub_Category' is null then "Empty" else 'Sub_Category' end as 'Sub_Category', count() as 'count';
4q = limit q 2000;

Sub-Category	Count of Rows
Accessories	775
Appliances	466
Art	796
Binders	1,523
Bookcases	228
Chairs	617
Copiers	68
Envelopes	254
Fasteners	217
Furnishings	957
Labels	364
Machines	115
Paper	1,370
Phones	889
Storage	846
Supplies	190
Tables	319
Empty	4

Analytics SAQL Developer Guide

Null Operators

Replace Null Values with case

See Also