Skip to main content
Announcements
Marching toward a simplified navigation! READ ON

Qlik Replicate Transformation: Source Lookup - CLOB datatype - limitations on string functions

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
Michael_Litz
Support
Support

Qlik Replicate Transformation: Source Lookup - CLOB datatype - limitations on string functions

Last Update:

Sep 28, 2023 8:58:55 AM

Updated By:

Sonja_Bauernfeind

Created date:

Sep 30, 2022 1:12:02 PM

CLOB data types have a limitation in that no string functions can be performed on them in a field transformation. There will be no errors in the log file, just that the field will be NULL.

In this article, we demonstrate source lookup functions and provide an example of replacing line feeds and carriage returns in LOB.

Environment

Qlik Replicate 
Table/Field level transformation

Field Transformation

On our SQL source table, we use the source lookup function under Data Enrichment to retrieve the CLOB field value and then apply a string function (replaceChars). 

By using the source lookup function on the field, it is returned to the Qlik Replicate task in a form where a string function can be applied.

For more information on Data Enrichment and the source_lookup functions see Data Enrichment.

The general form of the function is as follows:

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

In our example, we perform the lookup on the MyClob table:

source_lookup(1,'DBO','MyClob','MyClob','MyID=?',$MyID)

We follow up by wrapping the lookup in a replacechars function:

ReplaceChars(source_lookup(1,'DBO','MyClob','MyClob','MyID=?',$MyID),'L','@')

Expression Builder.png

Breaking up the parameters:

NO_CACHING is important to ensure it keeps changing for each value and doesn’t re-use values
DBO is the schema in Oracle
MyCLOB is the table
MyClob is the field that we want returned
MyID=? is the predicate for the lookup 
$MyID is the value from the field we are using in the predicate in place of =?

Performing the lookup may impact performance and will need to be tested to see if it meets all of your latency criteria. 

Reference screenshots and string examples to demonstrate the results of this exercise on the target table:

View of the Source DDL and data for MyClob table:

source ddl and data for myclob table.png

source ddl and data fro myclob table image 2.png

Target data for MyClob table (NO Transformations):

target data for myclob table no transformation.png

Target data for MyClob table (ReplaceChars() function on MyString and MyClob fields):

Note: No errors will be displayed in the task log, but null values will be present in the field.

MyString field transformation: replaceChars($MyClob,'M','@')

MyCLOB field transformation: replaceChars($MyClob,'L','@')

target data for myclob table with replacechars.png

Target data for MyClob table (ReplaceChars(SourceLookup) function on MyClob fields):

Note: You will see that the replaceChars function worked.

MyString field transformation: replaceChars($MyClob,'M','@')

MyCLOB field transformation: ReplaceChars(source_lookup(1,'DBO','MyClob','MyClob','MyID=?',$MyID),'L','@')

target data for myclob table with replacechars and lookup.png

 

You can further test different string functions on the Clob field once the source lookup returns the value.

Example: Replace Line Feed and Carriage Returns in LOB

ReplaceChars(source_lookup(1,'DBO','table','field','id=?',$id),X'0A','|')

 

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.

 

 

Labels (1)
Version history
Last update:
‎2023-09-28 08:58 AM
Updated by: