Problem with calculate size/length on column.

[data_engineer_whop]

Hi ,

I have question  to  calculate length or size  with type of column NCLOB /CLOB.  This column is document  pulled from  MOngo db and typical length function returns  0 or null. We tried to do it via global rule and direct operation on column in specific table. 

Thanks for any tips and  support. 

Regards

[SachinB]

Hello @data_engineer_whop ,

LENGTH() is designed for VARCHAR2 / CHAR, not for LOBs.

SELECT LENGTH(your_clob_column) FROM your_table; is always returns zero or null

It appears that is not supported when LOB/CLOB/NLOB data types return zero or null.

Regards,
Sachin B 

[data_engineer_whop]

Yes but when we  try to do  length(X) it doesn t give us  number of bytes ..there is still null. Here is a problem . Number of bytes would be still nice for us. 

Any ideas ?

[Dana_Baldwin]

Hi @data_engineer_whop 

Just to confirm, the LOB column for the row you are working with is not NULL, is that correct? If it is NULL, a check of it's length will always be NULL as it is undefined and mathematical checks of it cannot be determined. Could you use an IFNULL then 0 in your transformation?

Thanks,

Dana

[DesmondWOO]

Hi @data_engineer_whop ,

It appears that we cannot apply any functions to the column '_doc'. I recommend using a function on the target table to get the length. For example, in SQL Server, I can use LEN() to determine the length of the JSON data.

Regards,
Desmond

[data_engineer_whop]

@Dana_Baldwin  &  @DesmondWOO  thanks  for your answers :). We don't care honestly  if we have 0 or null as far as it is not result of length in bytes or in number characters. So the  information  below  that telling us  to use on blob  length function  to get number of bytes is wrong 😞

[Dana_Baldwin]

Hi @data_engineer_whop 

I think the hint in the software is correct for endpoints that support data enrichment, but unfortunately MongoDB is not an endpoint that supports data enrichment. I apologize for not checking that before, it is documented here:

Functions | Qlik Replicate Help

It appears that MongoDB supports this via:

• $strLenCP → Counts the number of UTF-8 code points (characters).
• $strLenBytes → Counts the number of bytes in the UTF-8 encoding.

Based on this I am not sure why there is a limitation within Qlik Replicate. If this is a feature you would like to see in the product, I encourage you to please submit a feature request here which goes directly to our Product Management team. Other users will have the opportunity to vote on it to increase the chances at adoption (if there is no technical obstacle) and to set the priority: https://community.qlik.com/t5/Ideas/idb-p/qlik-ideas

Thanks,

Dana