https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/ta-p/1805653 <P style="font-style: italic;">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.</P> <H3 id="toc-hId--525350720"><FONT color="#339966"><STRONG>Field Transformation</STRONG></FONT></H3> <P>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.&nbsp; 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.</P> <P><STRONG><FONT color="#339966">The general form of the function is as follows:</FONT></STRONG></P> <P class="lia-indent-padding-left-30px"><FONT face="courier new,courier">target_lookup(TTL,'SCHM','TBL','EXP','COND',COND_PARAMS)</FONT></P> <P><BR /><FONT color="#339966"><STRONG>For this example in am doing the lookup into the MLTest table:</STRONG></FONT></P> <P class="lia-indent-padding-left-30px"><FONT face="courier new,courier">target_lookup('<SPAN><SPAN class="ui-provider byh byi c d e f g h i j k l m n o p q r s t byj byk w x y z ab ac ae af ag ah ai aj ak">NO_CACHING</SPAN></SPAN>','dbo','MLTEST','Insert_Date','EMP_ID=?',$EMP_ID)</FONT></P> <P><FONT face="courier new,courier">NO_CACHING</FONT> is important to ensure it keeps changing for each value and doesn’t re-use values<BR /><FONT face="courier new,courier">dbo</FONT> = target schema<BR /><FONT face="courier new,courier">MLTEST</FONT> = target table name<BR /><FONT face="courier new,courier">Insert_Date</FONT> = the field name<BR /><FONT face="courier new,courier">EMP_ID</FONT> = the PK field name for the table in the database syntax<BR /><FONT face="courier new,courier">$EMP_ID</FONT> is the replicate internal representation of the PK field for the table</P> <P><STRONG>NOTE:</STRONG><SPAN>&nbsp;</SPAN>That the lookup may have a performance implication and will need to be tested to see if it meets all of your latency criteria.&nbsp;</P> <P><STRONG>NOTE:</STRONG><SPAN>&nbsp;</SPAN>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.</P> <P><FONT color="#339966"><STRONG>The complete transformation code as follows:</STRONG></FONT></P> <P>&nbsp;</P> <P>&nbsp;</P> <LI-CODE lang="markup">CASE WHEN $AR_H_OPERATION = 'INSERT' then datetime('now') ELSE target_lookup (1,'dbo','MLTEST','Insert_Date','EMP_ID=?',$EMP_ID) END</LI-CODE> <P>&nbsp;</P> <P>&nbsp;</P> <P>This expression is created for the Add Column&nbsp;transformation Insert_Date as seen in the screen shot below.</P> <P><span class="lia-inline-image-display-wrapper lia-image-align-center" image-alt="Target Lookup Insert_Date.PNG" style="width: 999px;"><img src="https://community.qlik.com/t5/image/serverpage/image-id/54401i09E04A2148FBB5C4/image-size/large?v=v2&amp;px=999" role="button" title="Target Lookup Insert_Date.PNG" alt="Target Lookup Insert_Date.PNG" /></span></P> <P>&nbsp;</P> <P>&nbsp;<STRONG>NOTE:</STRONG><SPAN>&nbsp;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.</SPAN></P> <P><SPAN>&nbsp;<STRONG>NOTE:</STRONG>&nbsp;Not all target endpoints support the target lookup function. Snowflake does not support&nbsp;this although there is a way to get the same functionality. Please check out the following article:&nbsp;&nbsp;<A href="https://community.qlik.com/t5/Knowledge/Qlik-Replicate-Transformation-column-INSERT-DATETIME-is/ta-p/1949728" target="_blank" rel="noopener">Qlik Replicate: Transformation column INSERT_DATETIME is overwritten with Null value when record is updated in Snowflake Target</A>&nbsp;</SPAN></P> <P><STRONG>NOTE:</STRONG><SPAN>&nbsp;The above target lookup uses the SQL server specific <STRONG>"?"</STRONG> <FONT face="courier new,courier">'EMP_ID=?'</FONT>and if you are doing this same lookup into an Oracle database use must use the Oracle specific <STRONG>":"&nbsp;<FONT face="courier new,courier">'EMP_ID=:'</FONT></STRONG></SPAN></P> <H4><FONT color="#339966"><STRONG>Environment</STRONG></FONT></H4> <P class="lia-indent-padding-left-30px"><LI-PRODUCT title="Qlik Replicate" id="qlikReplicate"></LI-PRODUCT>&nbsp;<BR />Table/Field level transformation</P> Wed, 26 Apr 2023 12:16:49 GMT Michael_Litz 2023-04-26T12:16:49Z https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/ta-p/1805653 <P>Transformation, Target Lookup, Add Column, Insert_Date field</P> Wed, 26 Apr 2023 12:16:49 GMT https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/ta-p/1805653 Michael_Litz 2023-04-26T12:16:49Z https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/tac-p/2025224#M8159 <P>Does this date get wiped out when the table is reloaded?</P> Fri, 13 Jan 2023 00:26:51 GMT https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/tac-p/2025224#M8159 zacker20 2023-01-13T00:26:51Z https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/tac-p/2025226#M8160 <P>Hi Zacher20,</P> <P>Yes, this field value would get cleared/reset when the table was reloaded.&nbsp;</P> <P>Thank you, <BR />Michael Litz <BR />Technical Support Engineer</P> <P>&nbsp;</P> Fri, 13 Jan 2023 00:41:20 GMT https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/tac-p/2025226#M8160 Michael_Litz 2023-01-13T00:41:20Z https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/tac-p/2025227#M8161 <P>How would I avoid reloading a table when using sql server ms cdc during a DDL change?</P> Fri, 13 Jan 2023 00:42:06 GMT https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/tac-p/2025227#M8161 zacker20 2023-01-13T00:42:06Z https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/tac-p/2025228#M8162 <P>Hi Zacker20,</P> <P>There are limitations on DDL's with ms cdc source, so depending on the DDL you may not have to do anything.</P> <UL> <LI>Limitations due to the requirement to have MS-CDC enabled on all source tables include: <UL> <LI>Rename column is not supported</LI> <LI>Truncate table is not supported</LI> <LI>WRITETEXT and UPDATETEXT utilities are not supported</LI> </UL> <P>For more information on MS-CDC limitations, see the Microsoft Help.</P> </LI> <LI> <P>Limitations when capturing DDL operations are as follows:</P> <UL> <LI>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.</LI> <LI>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.</LI> </UL> </LI> </UL> <P><SPAN>Thank you,</SPAN><BR /><SPAN>Michael Litz</SPAN><BR /><SPAN>Technical Support Engineer</SPAN></P> Fri, 13 Jan 2023 00:56:27 GMT https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/tac-p/2025228#M8162 Michael_Litz 2023-01-13T00:56:27Z https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/tac-p/2025232#M8163 <P>The table level ddls are not transferred to the target because we are using cdc not log based replication.</P> <P>wouldn’t I lose data when the table gets suspended&nbsp;</P> Fri, 13 Jan 2023 01:24:18 GMT https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Transformation-Target-Lookup-Insert-Date/tac-p/2025232#M8163 zacker20 2023-01-13T01:24:18Z