Customizing Data with Qlik Replicate

Troy_Raney — Mon, 12 Jun 2023 06:34:30 GMT

This session addresses:

How to Transform Date Fields
Source and Target Lookups
Table/Field vs Global Transformations
Adding Filters to Tasks

Environment

Qlik Replicate

00:00 - Intro

01:10 - What does Replicate do?

01:43 - Create a Global Transformation

03:52 - Renaming Tables

04:46 - Review tables in SQL

06:14 - Adding Update Date field

07:52 - Testing Expressions

09:12 - Demo CDC - Updates

09:37 - Adding Table level Insert Date

12:43 - Adding functionality to Snowflake

13:12 - Joining data from two tables

16:45 - Table Level Transformations

18:18 - How to investigate errors

20:08 - Where to find Help documentation

20:44 - Target Lookup Article

21:39 - Source Lookup Article

22:15 - Q&A: SaaS version of Replicate?

22:42 - Q&A: Are complex SQL statements possible?

23:14 - Q&A: Do Qlik Sense and Qlik Replicate work together?

23:27 - Q&A: Can you add Load Time as a column?

24:13 - Q&A: Can you hide sensitive information?

25:00 - Q&A: Where are the Replicate Log Files?

25:36 - Q&A: Which log files help troubleshoot?

26:14 - Q&A: Can you Transform for different sources?

27:22 - Q&A: How to setup Notifications?

28:50 - Q&A: How to transform UTC to local time?

29:23 - Q&A: How to combine First and Last name?

30:05 - Q&A: Are Full Loads necessary with Transformations?

Resources:

Qlik Replicate Supported End Points

Qlik Replicate Expression Functions

Transformation: Target Lookup - Insert_Date

Transformation: Source Lookup - Oracle ROWID

Q&A:

Q: Can you use SAS Datasets as Source?

A: I am not sure of this as I don't see it listed specifically in the support matrix in the user guide.
It would be best to reach out to your sales manager for confirmation on this.

Q: Can your target source/table be a QVD?

A: No A QVD (QlikView Data) file is a file containing a table of data exported from Qlik Sense.
QVD is a native Qlik format and can only be written to and read by Qlik Sense or QlikView.

Q: What about SAP HANA as target?

A: Yes, Replicate does support SAP Hana as a target endpoint.

Q: How big data volume these transformations can handle?

A: I have seen replicate task with transformation handle a very large volume of transactions.
The performance will vary on each sites specific configuration. Lookups do add a additional overhed due to running the select staement.

Q: Sequel Lite or SQL Lite language for transformation?

A: SQLite is the language used for transformation expression
https://www.sqlite.org/index.html

Q: Is there like cheat sheet for important and common used Transformation implementations?

A: Not necessarily a cheat sheet but there is a growing number of article in the Qlik community that discuss different transformation.
https://community.qlik.com

Q: Hello, is it possible to only track changes, listening to INSERT/UPDATEs, without replicating the table?

A: Yes, if I understand correctly you do not want the base table updated in the target only a table with the changes that occured.
Please Store Changes Setting under Change Processing for 2 options on only storing the changes for a table.

Q: Does Replicate work with Progress databases?

A: Yes, it is supported as either a source or a target endpoint.

Q: Can you avoid record deletion adding a delete date?

A: Yes, you can use an operaqtion indicator function in a transformation to do what we refer to as a soft delete.

Q: Is it correct that Replicate doesn't update lookup values (used with source_lookup() function) when they're change after the initial load?

A: Replicate will run the lookup function for every record that changes on the source as it is written to the target.

Q: Is it possible to start the task automatically again if there was a critical error?

A: Not that I know of from inside of replicate. You can however set up notifications that will send out an alert on task status.
It may be possible to script something that would monitor these and then do a command line that would issue the restart.

Click here for video transcript

article Customizing Data with Qlik Replicate in Official Support Articles

Customizing Data with Qlik Replicate

Environment