Cognos Analytics

How can I check if the field is numeric. IsNumeric function does not seem to work in Cognos Analytics 11.1.7

------------------------------
Raj Kavalla
------------------------------

It really depends on what database you're using in combination with Cognos. That will provide you information on what database specific functions you could use. For instance when using SQL you could use TRY_CAST function in the sql retrieving the table (e.g. in the data module).

An alternative is using like_regex. See the example below which will retrieve FALSE or TRUE based the value. You might need to tweak to expression to fit your purpose. Succes!

like_regex ( <input_column>; '^\d+\.?\d+\,?\d+$' )

------------------------------
Michiel Schakel
------------------------------

Original Message:
Sent: Mon May 15, 2023 09:02 AM
From: Raj Kavalla
Subject: How can I check if the field is numeric.

How can I check if the field is numeric. IsNumeric function does not seem to work in Cognos Analytics 11.1.7

------------------------------
Raj Kavalla
------------------------------

Another (quick) idea: Replace 0-9 by '' - and separators like . or , if needed as well - and check if the rest of the string is empty

------------------------------
Philipp Hornung
Business Intelligence Manager
Techniker Krankenkasse
Hamburg Germany
#IBMChampion
------------------------------

Original Message:
Sent: Tue May 16, 2023 02:48 AM
From: Michiel Schakel
Subject: How can I check if the field is numeric.

It really depends on what database you're using in combination with Cognos. That will provide you information on what database specific functions you could use. For instance when using SQL you could use TRY_CAST function in the sql retrieving the table (e.g. in the data module).

An alternative is using like_regex. See the example below which will retrieve FALSE or TRUE based the value. You might need to tweak to expression to fit your purpose. Succes!

like_regex ( <input_column>; '^\d+\.?\d+\,?\d+$' )

------------------------------
Michiel Schakel

Original Message:
Sent: Mon May 15, 2023 09:02 AM
From: Raj Kavalla
Subject: How can I check if the field is numeric.

How can I check if the field is numeric. IsNumeric function does not seem to work in Cognos Analytics 11.1.7

------------------------------
Raj Kavalla
------------------------------

The are not "Is<type>" or "Safe/try cast" built-in functions in the query engine.

You can use the regex capability.

This basic example returns a non-null string which matches a numeric literal which can include an optional leading sign. This expression can be referenced in another expression which performs a CAST to an integer type.

case when occurrences_regex (   '[+-]?[0-9]+\b'  , [REGEX].[CASE].[C1]  ) > 0then  [REGEX].[CASE].[C1]  else  NULLend

------------------------------
NIGEL CAMPBELL
------------------------------

Original Message:
Sent: Mon May 15, 2023 09:02 AM
From: Raj Kavalla
Subject: How can I check if the field is numeric.

How can I check if the field is numeric. IsNumeric function does not seem to work in Cognos Analytics 11.1.7

------------------------------
Raj Kavalla
------------------------------