How to remove leading zeroes from any column in OLAP table?

Hi,

PQL: It depends on the data - If number of zeroes is always the same you can try to use RIGHT/SUBSTRING functions. You can also try to use DELETE_CHARACTERS/MAP_CHARACTERS. Unfortunately PQL version of LTRIM function deletes only whitespace character, not digits.

LTRIM in PQL documentation link: https://docs.celonis.com/en/ltrim.html

Vertica SQL (Celonis Backend): the best and easiest option: use TRIM function with 'LEADING' keyword as in below example:

Documentation: https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/String/LTRIM.htm 

Best Regards,

Mateusz Dudek

Hi @chirag.sabha12,

In addition to @deleted deleted, if you are in an analysis/view and therefore have to use PQL, you can use the option TO_FLOAT(<colum>) to make it a decimal which automatically removes leading zeroes. Note that the column should be right formatted as specified at: TO_FLOAT (celonis.com).

Kind regards,

Jan-peter

Hi,

I just use TO_INT before the column name, like this: TO_INT(table.column)

It cuts away any amount of leading zeroes.

BR,

Jenny