Creating Filters for table replication

[williamp]

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’

[DesmondWOO]

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

[williamp]

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.

[aarun_arasu]

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

[DesmondWOO · Accepted solution]

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

[DesmondWOO]

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