Qlik Replicate Null Range Filter

[CC3]

Hello:

Looking for a way to do a range filter on an integer field to include both a number and a null value.  Data is coming from a MySQL database, the target is SQL Server, and the task is Full Load + CDC.  I understand how to add the range filter for the specific integer value (Equal To = 12346), but cannot figure out if it's possible to add NULL in that Range section as well.  I've tried:

Equal To = [N]

Equal To NULL

Equal to Null

Equal to null

These don't work - not that I would expect them to, given it's not how you filter null records in SQL.  I've tried using the Record Selection Condition section, and while it works, it's not at all performant.  For reference, the original range filter without factoring in nulls runs in less than a second.  Using a few variations in the Record Selection Condition section returned in 90 minutes, and then in 55 minutes upon retry.  The total records including nulls to be transferred is about 42,000, with maybe two hundred being null.  

I've found some other links on the board - https://community.qlik.com/t5/Official-Support-Articles/Filter-for-last-90-days-of-data-in-Qlik-Repl... - and it looks like a passthru filter may be appropriate.  Running the desired filter query directly on my source database returns in less than 5 seconds, so I'm thinking the passthru query should be nearly as performant.  

Aside from a passthru query, is there any way to add a Null inclusion in the table range filter?  Or a way to speed up the Record Selection Condition?  I've read that the latter may be slow and there's not much that can be done about that. 

[Steve_Nguyen · Accepted solution]

you can try something like:

You can do the following to replace NULLS with blank spaces in Replicate:
1) Under the task Designer mode, go to Global Rules if you want to apply to every and each table/columns or select the tables you want to apply this and go to transform
2) Add a new transformation with option: "Replace column value"
3) Add this to the expression:
ifnull($AR_M_SOURCE_COLUMN_DATA,' ')

That should do it, please test and let us know the outcome.

[CC3]

Giving this a shot.  Since the field is an integer, I replaced the null value with -111 as follows:  ifnull($AR_M_SOURCE_COLUMN_DATA, -111).  

I added a second range filter to look for this id field = -111, and so far I'm not seeing those null records in the target table.  Target table prep mode is drop and create.  

[Heinvandenheuvel · Accepted solution]

For full-load the passthru with 'IS NULL' or  'IS NOT NULL' as appropriate is most effective. Add that for sure.

You picked -111 as 'special' value for testing. Fine.  Does that mean that no negative values are allowed?

If so you could just filter on '> 0' as that will exclude rows with NULL value in the filter column. 

fwiw,

Hein.

[CC3]

The filter > 0 is a good thought, but the field I'm filtering on is like a company id.  So, the target only needs to contain results for one specific id and those that are null.  Trying the passthru option now.  

Made the changes on the replicate server for passthru queries, but the option is still not available in the EM console.  Researching further.

[CC3]

Update: 

The passthrough option is not appearing on the server after following the listed steps.  I created a separate post for that here: https://community.qlik.com/t5/Qlik-Replicate/Enable-Passthrough-Filter-on-Replicate/td-p/1993506

@Steve_Nguyen Interested in your thoughts about my response to you earlier.   

DOUBLE UPDATE: passthrough option is enabled and solves the initial issue well.  I still am curious if there's a way to check for null values in the Range filter section.  Leaving this open for that purpose.

[Heinvandenheuvel]

>>  I still am curious if there's a way to check for null values in the Range filter section.  

I believe there is. You just need to look lower on the 'ranges' form where it offers 'Record Selection Condition' and 'expression builder' you can speak SQLite there (Google is your friend) and you can us 'isnull' tests and the likes.

Hein.

[CC3]

Ah.  I tried that initially and was able to get it to work, but the runtimes for that specific table reload ballooned to about an hour or so.  Range filter for the specific non-null records reloaded in a few seconds, and the passthrough query is running about the same.  Querying the source data set on my local database connection medium returns results in a few seconds as well.