Qlik Replicate: Examples filtering NULL values on an AND or OR condition

[KellyHobson]

Introduction

This article outlines different methods for filtering out NULL values on a logical AND condition or a logical OR condition in a Full Load + CDC task.

Example dataset Persons:

CREATE TABLE Persons(
PersonID int,
LastName char(8),
FirstName char(8),
entryType char(8))

INSERT INTO Persons VALUES(1,'smith','mike','bronze')
INSERT INTO Persons VALUES(2,'lee','tom','silver')
INSERT INTO Persons VALUES(3,'thompson','lisa','bronze')
INSERT INTO Persons VALUES(4,'larry',NULL,'bronze')
INSERT INTO Persons VALUES(5,'williams',NULL,'gold')
INSERT INTO Persons VALUES(6,'burke','carol','gold')

OR example

In this use case, we want entryType=Bronze OR FirstName is not null.

Thus there are 3 test cases where the record makes it to the target:

1. entryType = Bronze, FirstName is NULL
2. entryType = Bronze, FirstName is not NULL
3. entryType = Gold or Silver, FirstName is not NULL

We apply the Record Selection Filter:

$FIRSTNAME != '' OR $ENTRYTYPE=='bronze'

Result on target:

1 smith mike bronze
2 lee tom silver
3 thompson lisa bronze
4 larry bronze
6 burke carol gold

AND example

In this use case, we want entryType=Bronze AND FirstName is not null.

We apply 2 filter types:

a. Filter Condition where we exclude Range=null

 

b. Record Level Condition $ENTRYTYPE=='bronze'

Thus there is 1 test cases where the record makes it to the target:

1. entryType = Bronze, FirstName is not NULL

Result on target:

1 smith mike bronze
3 thompson lisa bronze

Bonus round

If this was a full load only task, then you would want to take advantage of full load passthru filters.  This applied the filter on the source before you bring into Replicate.  Note below example in Oracle syntax because querying Oracle source.

OR example

Full load passthru filer:

((ENTRYTYPE='bronze') or (FirstName IS NOT NULL))

Result

1 smith mike bronze
2 lee tom silver
3 thompson lisa bronze
4 larry bronze
6 burke carol gold

AND example

Full load passthru filter:

((ENTRYTYPE='bronze') and (FirstName IS NOT NULL))

Result

1 smith mike bronze
3 thompson lisa bronze

Environment

• Qlik Replicate 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Related Content 

Filter for last 90 days example