Solved: String truncation during transformation - Qlik Community

sergsyb · ‎2022-09-06

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?

john_wang · ‎2022-09-06

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

Michael_Litz · ‎2022-09-06

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

View solution in original post

john_wang · ‎2022-09-06

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

sergsyb · ‎2022-09-06

Thanks John,

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

sergsyb · ‎2022-09-06

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?

sergsyb · ‎2022-09-06

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.

sergsyb · ‎2022-09-06

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

Heinvandenheuvel · ‎2022-09-06

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

SUBSTR

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

sergsyb · ‎2022-09-06

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

john_wang · ‎2022-09-06

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

sergsyb · ‎2022-09-06

John you wrote - what's the source DB?

Source DB is PostgreSQL 12

String truncation during transformation

General Question