topic Re: String truncation during transformation in Qlik Replicate

String truncation during transformation

sergsyb — Tue, 06 Sep 2022 09:42:01 GMT

I am sorry for may be trivial question, but in which way I can do transformation and truncate original string.
For example

On the Source we have column with text datatype. (very long string)
On the Target I would like to truncate original long string and fit in char(20) for example

If I only define new datatype for this column STRING (20) in Output of Transorm tab, new table with column char(20) creates on the target, but data for FULL LOAD contain long original string. As result target get error - original string could not fit in char(20)

Which expression i should add to transformation to truncate original long string to 20 character?

Re: String truncation during transformation

john_wang — Tue, 06 Sep 2022 10:22:49 GMT

Hello @sergsyb ,

I think you are almost there. You may add function RTRIM() see the Supported functions , a sample:

where NAME is the column which you want to trim.

However please take note you may get many warning messages ("Some of the source data was truncated in the target database") which reduce the performance. and I do not think the warning can be suppressed.

Hope this helps.

Regards,

John.

Re: String truncation during transformation

sergsyb — Tue, 06 Sep 2022 10:36:15 GMT

Thanks John,

But I have tried to do it with help rtrim($c3,20) but it can't truncate data
See attached screenshot

Re: String truncation during transformation

sergsyb — Tue, 06 Sep 2022 10:51:02 GMT

Sorry John, When I wrote previous reply I just checked with ExpessionBulder and ExpessionBulder did n't show truncation. But now, I run task and show all data was truncated and fit into varhar (20).

Thanks John.

But why ExpessionBulder doesn't show truncation properly?

Re: String truncation during transformation

sergsyb — Tue, 06 Sep 2022 11:11:45 GMT

John , I was happy too early. With help of transformation rtrim($c3,20). I don't get error now, but string was loaded as NULL into columnn which I expected will have truncated data.

So the question remain the same.

Re: String truncation during transformation

sergsyb — Tue, 06 Sep 2022 11:24:21 GMT

John, according to documentation- Second parameter for rtrim function is not expected length for truncation.

The rtrim(x,y) function returns a string formed by removing all characters that appear in y from the right side of x.

So if data in c3 column stores as 'abcdef'
rtrim($c3, 'ef') will retunrn 'abcd'

But what function will help to simply truncate string?
Thanks

Re: String truncation during transformation

Heinvandenheuvel — Tue, 06 Sep 2022 12:55:37 GMT

>> But what function will help to simply truncate string?

SUBSTR

https://www.sqlitetutorial.net/sqlite-functions/sqlite-substr/

Re: String truncation during transformation

sergsyb — Tue, 06 Sep 2022 13:27:08 GMT

Thanks Heinvandenheuvel

Function SUBSTR is really truncate String to desired length. But it works only for String which defined on the Source as Char or Varchar datatype.
But in my case I have LOB (text datatype) on the Source. Replicate when uses SUBSTR function for LOB column just send NULL to the target instead of truncated string

Re: String truncation during transformation

john_wang — Tue, 06 Sep 2022 13:50:28 GMT

Hello @sergsyb , copy @Heinvandenheuvel ,

But in my case I have LOB (text datatype) on the Source. Replicate when uses SUBSTR function for LOB column just send NULL to the target instead of truncated string

This is the reason. All the functions cannot be used for LOB data types (include BLOB/CLOB and other data types which will be handled as LOB), any transformation will lead the LOB columns become NULL. The only supported transformation is remove the column.

BTW, what's the source DB?

Regards,

John.

Re: String truncation during transformation

sergsyb — Tue, 06 Sep 2022 14:08:54 GMT

John you wrote - what's the source DB?

Source DB is PostgreSQL 12

Re: String truncation during transformation

john_wang — Tue, 06 Sep 2022 14:15:49 GMT

Hello @sergsyb ,

Please allow me some time, Let me see if we can find a workaround for it or not.

Will get back to you later.

Regards,

John.

Re: String truncation during transformation

john_wang — Tue, 06 Sep 2022 15:36:43 GMT

Hello @sergsyb ,

I think the most easy way is using MATERIALIZED VIEW, we cut the CLOB column to 20 chars in the view, for example:

create MATERIALIZED VIEW public.testtext_vw as select id,name,notes,substring(notes from 1 for 20) from public.testext; REFRESH MATERIALIZED VIEW public.testtext_vw;

Unfortunately PostgreSQL Logical Replication does not support MATERIALIZED VIEW. See ...must be normal tables, not views, materialized views... .

So we have to get back to Replicate again. Let's use source_lookup function. In your current transformation, change the expression to

source_lookup('NO_CACHING','public','testext','substring(notes from 1 for 20)','id=?',$id)

where 'public' is the schema name, 'testtext' is the table name, 'notes' is CLOB column, 'id' is the table PK.

I hope it solve the issue.

Regards,

John.

Re: String truncation during transformation

Michael_Litz — Tue, 06 Sep 2022 17:10:24 GMT

Hi @sergsyb ,

Please have a look at this source lookup function as it will do a lookup into the source field and it should return the LOB value as string and then the substr function should work.

Transformation: Source Lookup - Oracle ROWID 5/4 ( Ted )
https://community.qlik.com/t5/Knowledge/Transformation-Source-Lookup-Oracle-ROWID/ta-p/1805170

Please let me know if this helps out.

Thanks,
Michael

Re: String truncation during transformation

sergsyb — Tue, 06 Sep 2022 19:18:11 GMT

Hi John, Michael

You are simply the best. Thanks a lot for your suggestion re Lookup function.

Finally, with help of this function we managed to successfully replicate truncated LOB value to the target..