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

[Michael_Litz]

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','@')

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:

Target data for MyClob table (NO Transformations):

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 (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','@')

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.