Encoded string lenght for use in Immerse
Hello,
I have a table with a TEXT ENCODING DICT(16) column that I wish to use in Immerse in a custom dimension.
The custom expression involves the LENGTH or CHAR_LENGTH function, that, both, work only for ENCODING NONE string, as per-documentation.
In addition, it isn't possible to use an ENCODING NONE string in Immerse.
I didn't find a way to transform an encoded string into "encoding none" type using sql , and the only workaround I found is using REGEXP_COUNT(<encoded string>); this return a valid string length, using the correct search pattern.
Now the question is: Is there a better way than this one, in order to achieve a string length within a custom expression in Immerse for an ENCODING DICT string ?
Thank you
-
Hi Gianfranco,
The LENGTH or CHAR_LENGTH function is fully supported on a dictionary-encoded string and can be used in Immerse or the database, so I'm unsure I fully understand the issue.
An ENCODING NONE string cannot be used on expressions like GROUP BY, and DISTINCT without being converted into an ENCODED DICT with the ENCODE_TEXT function. -
Thanks Candido, maybe I'm missing something.
I'm referring to this part of documentation (Function and Operators page):
and to this in data type page
and to this error in Immerse trying to use the length function (with watchdog enabled):
Immerse is trying to render a measure using the custom exspression " lenght(field1) "
where "field1" is encoded this way:
FIELD1 TEXT ENCODING DICT(16)
-
Hi Gianfranco,
When working with DICT ENCODED strings, the LENGTH and CHAR_LENGTH functions perform an implicit conversion of the string's data to NONE ENCODED. This operation is CPU-intensive and it's condiered expensive enough to be placed under the WATCHDOG control.
Therefore, it is limited to a certain number of rows that can be processed under the WATCHDOG. By default, the maximum number of rows that can be converted is set to 1M, which means that in order to use the function directly, you would need to change this parameter or disable the watchdog entirely.
Which expression are you using with regexp count?
Regards,
Candido -
Thanks for the explanation Candido.
For a 4,5M rows table using in a custom dimension :
case when LENGTH(field1) = 3 then 'TYPE_1' else 'TYPE_2' end
returns the Chart Render Error from watchdog, while using this expression :
case when REGEXP_COUNT(field1, '[A-Za-z0-9]') = 3 then 'TYPE_1' else 'TYPE_2' end
works well.
Given that I wish to avoid disabling watchdog and also using cpu-intensive tasks, it seems to me that REGEXP_COUNT is the best practice whenever I need to use the length function on numebr of rows > 1M.
Please sign in to leave a comment.
Comments
4 comments