Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
williamp
Contributor
Contributor

Creating Filters for table replication

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’

1 Solution

Accepted Solutions
DesmondWOO
Support
Support

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

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

5 Replies
DesmondWOO
Support
Support

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

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
williamp
Contributor
Contributor
Author

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
Support
Support

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
Support
Support

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

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
DesmondWOO
Support
Support

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

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!