In-DB Filter problem

[MsBindy]

I am using an In-DB filter tool but the number of records coming out of the T and F sides of the output do not equal the number of records going into the tool.

It seems pretty basic,  but I don't know why all of the false records  don't come out into the F stream.

What I do see is that the records that are missing have a null value in the field that I'm filtering.....but still shouldn't they come out of the False side?

When I count my records by Vendor_Type , this is what I have going into the filter

Null   - 80

CORP  - 70

EMPL 1783

PART 48

SOLE 20

I am trying to segregate the employee vendors from everyone else and then process each output differently.

I filter for "Vendor_Type" = "EMPL",  I get the 1783 records in the T output.

I get the CORP, PART, and SOLE records in the F output.

The Null Vendor_Type disappears.

[echuong1 · Accepted solution]

With the normal filter tool, everything that equates to TRUE (or 1) is output from T and everything else is output from F. With the InDB Filter, 1 is output from T BUT FALSE (or 0) is output from F.  If your condition equates to NULL, the record is not output at all.

To get around this, I suggest filtering for your desired value (EMPL) and null values. From there, have a second filter that isolates the null values. You can then union the F values from the first filter and the null values from the second filter to get your population.

[MsBindy]

Thanks.  I'll have to tuck that difference in the my long term memory  🙂

[mgiwa]

Hi,

Cause:

Alteryx generate and parses a query that just adds ‘NOT’ to whatever condition was specified in the filter for False part of the filter (rather than reverse the operator for each condition) and as a standard for both Oracle and Snowflake (NOT Condition always excludes records with Null on the condition field).

Solution:

use custom filter option and modify you condition to use NVL.

NVL("Vendor_Type",'') = "EMPL"