Using TriState Boolean Logic for handling of NULL values

[Cloud release: 2025.02.19]
[On-prem release: 2025 March]
[On-prem build: 25.03.19002] 

Added Support for TriState Boolean Logic

This enhancement enables the handling of cases where NOT NULL values were previously allowed.

We have aligned our functionality with the expected behavior found in most databases. When using the "not equal to" (<>) filter, records containing NULL values are excluded. This is because NULL signifies an unknown value, and comparisons involving NULL do not yield true or false. As a result, those rows will not be included unless specifically addressed.

This new way of handling NULL values may require you to modify your ETL job to check for full integrity in your data warehouse. I.e., you will need to ensure that all foreign keys have matching primary keys in the related tables. This can be performed by "left join" operations.

Example from InMemory ETL script:

IMPORT dProductTypeCategory = [ME].{SELECT a.Item as ProductID,
  case
    when a.Freight = 'yes' then 'Freight / Handling Fee'
    when b.ProductTypeCategory is null or b.ProductTypeCategory = '' then 'No Category'
    else b.ProductTypeCategory
  end
as ProductTypeCategory
FROM [dItem] a
LEFT JOIN tmpProductTypeCategory b ON a.Item = b.ProductID}