topic Re: Load a table with no Primary Keys or Unique Keys In Qlik Replicate in Qlik Replicate

Load a table with no Primary Keys or Unique Keys In Qlik Replicate

nabeelaslam1994 — Thu, 12 Jan 2023 17:06:09 GMT

Hello Team,

I am trying to see if how can i load a table which does not have a primary or unique keys in it. Is there a way to do a DISTINCT when loading a table into a target? I need all records to be distinct when loading into target.

Nabeel

Re: Load a table with no Primary Keys or Unique Keys In Qlik Replicate

Dana_Baldwin — Thu, 12 Jan 2023 18:00:18 GMT

Hi @nabeelaslam1994 ,

Will there be a PK/UI on the target table?

What is your source & target database product?

You can also define which columns the task should use to enforce uniqueness on the target. Generally speaking if a PK/UI is defined on the target, Replicate will use that, else it will use what is defined in the task (depends on the target endpoint).

More information can be found here: Defining transformations for a single table/view #Defining transformations for a single table/view | Qlik Replicate Help

Thanks,

Dan

Re: Load a table with no Primary Keys or Unique Keys In Qlik Replicate

nabeelaslam1994 — Thu, 12 Jan 2023 19:41:25 GMT

@Dana_Baldwin

Yes I am familiar with that but if the table has REALLY NO primary key/ UNIQUE Key combination in it, and there are duplicates record, How can we ignore those and just bring on into target? Some sort of DISTINCT feature.

Source Endpoint: DB2 for Iseries

Target Endpoint: Snowflake

Nabeel

Re: Load a table with no Primary Keys or Unique Keys In Qlik Replicate

Dana_Baldwin — Thu, 12 Jan 2023 21:41:07 GMT

Hi @nabeelaslam1994

There isn't a way to modify the select query against the source to use the distinct keyword. At the moment the only suggestion I have would be to make every column a key column in the table definition within Replicate. I will reply again if I find a different solution.

Thanks,

Dana

Re: Load a table with no Primary Keys or Unique Keys In Qlik Replicate

john_wang — Fri, 13 Jan 2023 01:56:08 GMT

Hello @nabeelaslam1994 ,

Totally agree with @Dana_Baldwin , no way to use a custom query within Replicate, maybe we can use other options which is out of Replicate. See how about to create a VIEW in DB400 to get distinct rows, and then use the VIEW as source table in Replicate task? Note it can be used in Full Load only. A sample:

CREATE VIEW apsupdb/johnvw2 AS (SELECT DISTINCT id,name FROM apsupdb/emp)

I hope this helps.

Regards,

John.

Re: Load a table with no Primary Keys or Unique Keys In Qlik Replicate

Heinvandenheuvel — Fri, 13 Jan 2023 03:52:15 GMT

There is a way to modify the source unload query but only the WHERE clause, not the FROM no SELECT part. This method is called 'passthru' queries. See links below. Unfortunately I don't think that'll solve your problem.

I suppose you not make any changes on the source to 'clean it up'? Like just delete the dups, or have a count of duplicates column instead of actual duplicates? 99 out of 100 Replicate users are barely allowed to 'look' at the date and certainly not allowed to change the source data. Yes I know.

Your best alternative is a VIEW on the source as suggested.

Next best alternative is a VIEW on the target. Load to a 'helper' table and have the real table name be 'SELECT DISTINCT' view on the helper. Replicate will happily rename the target to 'helper'.

May I suggest the following older articles as background reading?

#https://community.qlik.com/t5/Qlik-Replicate/Filtering-duplicates-using-Fullload-Passthru-Filter/td-p/1891227
#https://community.qlik.com/t5/Official-Support-Articles/Filter-for-last-90-days-of-data-in-Qlik-Replicate/ta-p/1880761

Hein.

Re: Load a table with no Primary Keys or Unique Keys In Qlik Replicate

nabeelaslam1994 — Fri, 13 Jan 2023 15:18:09 GMT

Hello All,

I can not make a view in the source database and no changes are allowed except with a proper change request. I believe AS400 Sourcepoint does not allow to see a view in the table setting. I am trying to see if RRN option in source endpoint settings can control the dupes in target..

Nabeel

Re: Load a table with no Primary Keys or Unique Keys In Qlik Replicate

Heinvandenheuvel — Fri, 13 Jan 2023 15:33:08 GMT

>> no changes are allowed except with a proper change request.

That's Standard Operation Procedure.

It shouldn't stop you and you shouldn't be making the request, but whoever needs the target data. You tell your 'customer' that needs the Replication task that you can only deliver the optimal output with XXX changes done on the source. Let them fight the good fight or realize that their needs can not be met.

I'm thinking a view on target me be better for all involved. Biggest drawback is that too much data many be transferred.

Hein

Re: Load a table with no Primary Keys or Unique Keys In Qlik Replicate

Dana_Baldwin — Fri, 13 Jan 2023 15:36:46 GMT

Hi @nabeelaslam1994 ,

Yes, using RRN should also alleviate this issue.

Setting advanced connection properties | Qlik Replicate Help

For the sake of providing complete information, if you were to go with the route of defining all columns within Replicate as key columns, you would also need to enable upsert & merge.

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Implementing-UPSERT-and-MERGE-modes-by-applying-a/ta-p/1780009

Thanks,

Dana

Re: Load a table with no Primary Keys or Unique Keys In Qlik Replicate

nabeelaslam1994 — Fri, 13 Jan 2023 15:58:26 GMT

@Heinvandenheuvel The other issue with target database is SNOWFLAKE. I believe there is no key enforcement when it comes to snowflake.

Nabeel