topic Re: Creating Filters for table replication in Qlik Replicate

Creating Filters for table replication

williamp — Tue, 18 Jun 2024 18:30:57 GMT

I've been working on adding filters to our replication but found the Expression Builder is lacking proper explanation. I've written code that should work for SQLite and messed around with different formatting but it doesn't seems to work.

I am trying to test the filter below in one of my tables. I've written it a few different ways in the Expression Builder but it doesn't seem to output correctly. How should I be writing this expression?

select * from TABLENAME where GETTIME >= ‘2024-01-01’

Re: Creating Filters for table replication

DesmondWOO — Wed, 19 Jun 2024 02:51:56 GMT

Hi @williamp ,

Thank you for reaching out to Qlik Community.

Please try the following expression and let me know if it works.

date($GETTIME) >= '2024-01-01'

Regards,
Desmond

Re: Creating Filters for table replication

williamp — Wed, 19 Jun 2024 12:34:22 GMT

It looks like the expression builder accepts that.

Though, when put into the table filter the reload still ends up loading all the rows instead of just the ones that match the condition.

Re: Creating Filters for table replication

aarun_arasu — Wed, 19 Jun 2024 14:25:02 GMT

Hi @williamp ,

can you please go thru the below link and try out the passthru filter

https://community.qlik.com/t5/Qlik-Replicate/Pass-thru-filter/td-p/2411372

In case if still issue persist , please enable SOURCE_UNLOAD to Verbose and perform a reload and from the logs you could see the Select query which is generated by the replicate.

Validate the Select query or run them manually on the database and check out the output.

Regards

Arun

Re: Creating Filters for table replication

DesmondWOO — Wed, 19 Jun 2024 14:56:26 GMT

Hi @williamp ,

Please check the actual number of rows in the target table, rather than relying solely on the statistics displayed in the Replicate console. When using the expression builder, the filtering process is handled by Replicate. This means Replicate retrieves all records from the source table and then do filtering. That's why the console shows the number is same as the source table.

If your filter statement is simple, I would suggest defining it in the "Filter Conditions" for better performance.

Regards,
Desmond

Re: Creating Filters for table replication

DesmondWOO — Wed, 19 Jun 2024 15:03:08 GMT

Hi @williamp ,

In addition to checking the number of rows in the target table, you can also check the task log. For example,

[SOURCE_UNLOAD ]I: Unload finished for table 'dbo'.'des2' (Id = 1). 360 rows sent. 
[TARGET_LOAD ]I: Load finished for table 'dbo'.'des2' (Id = 1). 360 rows received. 0 rows skipped. Volume transferred 144000.

You can view the number of records retrieved from the source table and the number of records applied to the target table.

Regards,
Desmond