Qlik Replicate Transformation: Target Lookup - Insert_Date

[Michael_Litz]

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.

Field Transformation

A very common use case for a target table is to have an Insert Date field that populates when the record is initially written. The problem is when the record is updated, the original value in the target is overwritten with null. On my Target table from SQL, I am using the target lookup function under Data Enrichment in order to retrieve the Insert_Date field value during an update operation.  When the task table has an Add Column field called Insert-Date and you want to preserve the original date value from when the record was inserted into the target you need to do a target lookup to fetch the original value. In the case of Insert_Date we will use a target lookup function to query the target table and retrieve the field value, putting it into the Add Column that we defined and called Insert_Date. This target lookup function is documented in the replicate user guide and the on-line help.

The general form of the function is as follows:

target_lookup(TTL,'SCHM','TBL','EXP','COND',COND_PARAMS)

For this example in am doing the lookup into the MLTest table:

target_lookup('NO_CACHING','dbo','MLTEST','Insert_Date','EMP_ID=?',$EMP_ID)

NO_CACHING is important to ensure it keeps changing for each value and doesn’t re-use values
dbo = target schema
MLTEST = target table name
Insert_Date = the field name
EMP_ID = the PK field name for the table in the database syntax
$EMP_ID is the replicate internal representation of the PK field for the table

NOTE: That the lookup may have a performance implication and will need to be tested to see if it meets all of your latency criteria. 

NOTE: You will want to check if the record is being inserted for the first time or updated and only do the lookup if the operation is an Update. The following code shows how to implement this.

The complete transformation code as follows:

CASE
WHEN $AR_H_OPERATION = 'INSERT' then
datetime('now')
ELSE
target_lookup (1,'dbo','MLTEST','Insert_Date','EMP_ID=?',$EMP_ID)
END

This expression is created for the Add Column transformation Insert_Date as seen in the screen shot below.

 NOTE: Unfortunately data enrichment functions can not be tested on the screen, they must be saved and the task run for the transformation to be tested.

 NOTE: Not all target endpoints support the target lookup function. Snowflake does not support this although there is a way to get the same functionality. Please check out the following article:  Qlik Replicate: Transformation column INSERT_DATETIME is overwritten with Null value when record is ... 

NOTE: The above target lookup uses the SQL server specific "?" 'EMP_ID=?'and if you are doing this same lookup into an Oracle database use must use the Oracle specific ":" 'EMP_ID=:'

Environment

Qlik Replicate 
Table/Field level transformation

[zacker20]

Does this date get wiped out when the table is reloaded?

[Michael_Litz]

Hi Zacher20,

Yes, this field value would get cleared/reset when the table was reloaded. 

Thank you,
Michael Litz
Technical Support Engineer

[zacker20]

How would I avoid reloading a table when using sql server ms cdc during a DDL change?

[Michael_Litz]

Hi Zacker20,

There are limitations on DDL's with ms cdc source, so depending on the DDL you may not have to do anything.

• Limitations due to the requirement to have MS-CDC enabled on all source tables include:
  • Rename column is not supported
  • Truncate table is not supported
  • WRITETEXT and UPDATETEXT utilities are not supported

  For more information on MS-CDC limitations, see the Microsoft Help.

• Limitations when capturing DDL operations are as follows:

  • RENAME TABLE will no be captured. When a RENAME TABLE DDL is encountered, the task will continue capturing the changes without renaming the target table. If the task is stopped and resumed of if there is a recoverable error, the table will be suspended to allow the CT table to be manually aligned.
  • Table-level DDLs are not supported. When a table DDL is encountered, the DDL will be transferred to the target and the table will be suspended to allow the CT table to be manually aligned.

Thank you,
Michael Litz
Technical Support Engineer

[zacker20]

The table level ddls are not transferred to the target because we are using cdc not log based replication.

wouldn’t I lose data when the table gets suspended