Filter for last 90 days of data in Qlik Replicate part two

KellyHobson — Tue, 13 Sep 2022 11:26:31 GMT

This is an extension to Filter for last 90 days of data in Qlik Replicate. This article outlines how to set up a filter for the past 90 days worth of data via a full load pass thru filter or a record selection condition.

Example:

You run a task with a 90-day full load pass thru filter enabled. If you have 'TRUNCATE before load' or 'DROP and CREATE table' enabled (Task Settings-> Full load -> Full Load Settings -> Target Table Preparation), only the data in range will be transferred.

Once the full load completes, it continues to move forward with CDC. Let's say a few months go by and now your target table is not reflecting the last 90 days' worth of data. It is the original 3-month window plus inserts, updates and deletes since the last full load.

How do you limit the task to data within a specific range?

Below is an option workaround to this scenario.

Define your time periods and enforce them with record level conditions for CDC changes.

Postgres Source example where today's date is 9/12/22:

CREATE TABLE public.filtertest (id INT PRIMARY KEY, first_name VARCHAR(40), created_at TIMESTAMP);

INSERT INTO public.filtertest VALUES(1,'Kelly','2022-02-07 10:00:00');
INSERT INTO public.filtertest VALUES(2,'Laura','2022-01-07 10:00:00');
INSERT INTO public.filtertest VALUES(3,'Blair','2022-09-07 10:00:00');
INSERT INTO public.filtertest VALUES(4,'Liz','2022-08-07 10:00:00');
INSERT INTO public.filtertest VALUES(5,'Maddie','2022-07-14 10:00:00');
INSERT INTO public.filtertest VALUES(6,'Jordan','2022-03-14 10:00:00');

where Filter conditions are:

Plain text:

$created_at < '2022-09-12 10:00:00' AND $created_at > '2022-06-14 10:00:00'

created_at > current_date - interval '90' day

For the full load, only records with ID 3, 4,and 5 will apply on the target.

TEST:

UPDATE public.filtertest SET first_name = 'LARRY' WHERE id = 2;

Will not apply the change.

UPDATE public.filtertest SET first_name = 'LARRY' WHERE id = 4;

Will apply the change.

Environment

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.

article Filter for last 90 days of data in Qlik Replicate part two in Official Support Articles

Filter for last 90 days of data in Qlik Replicate part two

Environment

Related Content