topic Filtering duplicates using Fullload Passthru Filter in Qlik Replicate

Filtering duplicates using Fullload Passthru Filter

leonardoed — Wed, 09 Feb 2022 19:55:34 GMT

Hi All,

I`m trying to replicate a table from SAP HANA as a source to azure as target. By now I am bringing the last 13 months of data from the table, and I need this 13 months. For this I am using the Fullload Passthru Filter as follows:

Now comes the problem: Even filtering only the 13 last months, my dataset still have more than 500 million rows. And I am facing an error because I have used all my space on azure. I found that I have some duplicated rows ( when I select distinct it goes from 500kk to 30kk) that are needed on HANA but I wont need them on azure. So now comes my question: Can I do this select distinct filter using the Fullload Passthru Filter? Or is any other way to filter this when Qlik brings the data from the source? If not I will have to manage this directly on hana before replicating.

Thanks

Re: Filtering duplicates using Fullload Passthru Filter

Heinvandenheuvel — Thu, 10 Feb 2022 05:08:03 GMT

So you don't want all columns from the table, just those listed in the DISTINCT clause right. Really?

Hmm, the 'passthru' is implemented as TEXT for a WHERE clause.

The SELECT <column list> FROM table text is already in place. No option to inject a 'DISTINCT' there, plus there is no option for an alternative <column list>

I don't think there is a clever WHERE clause only thing you can do as alternative. If you google for 'SQL alternative to distinct' or similar you'll find several responses, but the ones I saw needed a JOIN in the FROM.

Your best bet is probably to fullload a VIEW, not the base table.

Hein

Re: Filtering duplicates using Fullload Passthru Filter

shashi_holla — Fri, 11 Feb 2022 23:31:22 GMT

If we don't need duplicate on the target, we would have to create a unique constraint on the target table so that duplicates get filtered out. And also the scenario looks like we will need to have different unique constraint between source and target.

Re: Filtering duplicates using Fullload Passthru Filter

Heinvandenheuvel — Sun, 13 Feb 2022 01:43:45 GMT

Shashi has an interesting suggestion.

Select the duplication controlling columns on the target to create a unique constraint after table creation.

Do not let Replicate drop and recreate, but truncate instead

Set the task error handling for 'apply conflicts' to 'Duplicate key when applying INSERT:
UPDATE the existing target record'. That way you'll just get the last row with duplicate key updating any prior ones.

hth,

Hein