Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. READ MORE

Filter for last 90 days of data in Qlik Replicate

100% helpful (1/1)
cancel
Showing results for 
Search instead for 
Did you mean: 
KellyHobson
Former Employee
Former Employee

Filter for last 90 days of data in Qlik Replicate

Last Update:

Jul 9, 2024 3:33:03 AM

Updated By:

Sonja_Bauernfeind

Created date:

Jan 13, 2022 10:46:42 AM

This article references two options for filtering the last 90 days worth of data on a date column in Qlik Replicate.

Additionally, the goal is to show a working example of full load pass thru vs record selection condition so users can adjust filters to different types or conditions.

Option 1: Full load passthru

The full load pass thru filter is more efficient since it filters directly on the source. For this to work, you need to use the exact syntax of the source database. It can only be applied to full load tasks, not change data capture mode.

Example with Oracle source where START_TRAN_DATE is of type DATE:

START_TRAN_DATE > sysdate - 90

To open with Full load Passthru available:

  1. Select the table you want to filter on in the Designer tab
  2. Hit CTRL + Table Settings

    1.png

  3. Click okay and save your task.

 

If you see the error:

Table 'x' cannot be reloaded because a passthrough filter is defined for it. Passthrough filters allow task designers to control SQL statements executed on source database tables during replication. To continue using passthrough filters, you must explicitly set "enable_passthrough_filter" to true in the "C:\Program Files\Attunity\Replicate\bin\repctl.cfg" file. Otherwise, remove the passthrough filter from this table and any other tables defined with passthrough filters. [1020439] (endpointshell.c:3716)

Then:

  1. Open C:\Program Files\Attunity\Replicate\bin\repctl.cfg

  2. Add:

    "enable_passthrough_filter": true

  3. Restart the Qlik Replicate Server service. 

Option 2: Record Selection Condition

This option can be used for full load and change data capture task but is not as efficient for large tables as full load passthru.  It does not have to be the exact syntax of the source, i.e the example below can work on many sources.  This is because the filter condition function is an SQLite function within Replicate.

An example where START_TRAN_DATE is of type DATE

$START_TRAN_DATE >= DateTime('Now', 'LocalTime', '-90 Day')

  1. Go to Task Designer
  2. Open Table Settings
  3. Click Filter
  4. Choose Expression Builder

    1. Fill out 1: Build Expression with:

      $START_TRAN_DATE >= DateTime('Now', 'LocalTime', '-90 Day')

    2. Click Parse Expression

    3. Fill out 2: Parse Expression with:

      2021-10-13 23:47:56

    4. Click Test Expression

      This should return 0.

      2.png

    5. Fill out 2: Parse Expression with:

      2021-12-13 23:47:56

    6. Click Test Expression

      This should return 1.

      3.png


  5. Click OK.

    4.png

  6. Save your task

 

Related Content:

Filter for last 90 days part two with postgres example


Environment:

Qlik Replicate 

 

The information in this article is provided as-is and to be used at own discretion. Depending on tool(s) used, customization(s), and/or other factors ongoing support on the solution below may not be provided by Qlik Support.

Labels (2)
Comments
Finnova
Contributor III
Contributor III

Hello Kelly,

1. Thank you for the example, that was exactly what I was talking about. 

2. We would like to propose this feature but unfortunatly the Ideations Page doesen't load on our side. Do customers like us have access to it?

Best regards,

Marc

Sonja_Bauernfeind
Digital Support
Digital Support

Hello @Finnova 

Every user should have access to the ideation section. Could you let me know what error you are getting so that we can forward this to our community team?

 

All the best,
Sonja 

AutomatedUser
Contributor II
Contributor II

@KellyHobson or @Sonja_Bauernfeind Following the guide (adding the entry to the config file, restarting services, holding CONTROL) does not appear to grant me access to the Full Load Passthrouh filter.  Any other suggestions to get this option available?

KellyHobson
Former Employee
Former Employee

Hey @AutomatedUser ,

Can you confirm if you have selected the table and holding down CTRL while you click 'Table Settings'?  

If you are still experiencing an issue, I suggest opening a support case so we can take a look at your environment.

Best,

Kelly 

AutomatedUser
Contributor II
Contributor II

@KellyHobson confirmed.  Even that did not provide access to the passthrough filter.  Case has been opened.

AutomatedUser
Contributor II
Contributor II

@KellyHobson I was able to work with support and determine that since I was attempting to access the passthrough filter via Enterprise Manager, that I needed highlight the target table, hold down CTRL and double-click the table to access the passthrough menu.  Highlighting the table, holding down CTRL and clicking on Table Settings does not appear to work in Enterprise Manager (only in the Replicate Console).

Version history
Last update:
2 weeks ago
Updated by: