Re: Placing text in a decimal column

Admin_Agrotec · 11-08-2021 08:45 AM

Hello All,
I am trying to place a “text” value in “decimal” column with a IF expresión but I am getting an error.

Expresion:

IF((SUM(SELECT(registros[pH], AND([_THISROW].[Punto de Muestreo]=[Punto de Muestreo],[Hora Fecha Inicio]>=[_THISROW].[Rango Fecha Inicio],[Hora Fecha Final]<=[_THISROW].[Rango Fecha Final]))) > 0), AVERAGE(SELECT(registros[pH], AND([_THISROW].[Punto de Muestreo]=[Punto de Muestreo],[Hora Fecha Inicio]>=[_THISROW].[Rango Fecha Inicio],[Hora Fecha Final]<=[_THISROW].[Rango Fecha Final]))), “NA”)

Error:

Value ‘NA’ in field ‘pH (Pro)’ cannot be converted to type ‘Decimal’.

Any insight would be appreciated.

Admin_Agrotec

PD. I tried set the column type to “TEXT” but it generates another issue:
does not work with the “Show_If” expression:

[pH (Pro)] > 0

Marc_Dillon

You can’t put text values into a Number or Decimal column.

You can maybe use

DECIMAL( [pD (Pro)] ) > 0

Admin_Agrotec

Thanks @Marc_Dillon , this worked. How would I set it to 2 decimal places?

Marc_Dillon

Are you asking how to validate a Decimal value in a Text column to 2 decimal places?

Admin_Agrotec

Yes

Marc_Dillon

ok.

First, I think you’re going to keep running into issues by putting decimal values in a text column, so I don’t really recommend that you continue with this.

But if you insist, perhaps this:

Valid_If = 

LEN( INDEX( SPLIT( [_THIS] , "." )  ,2 ) = 2

Admin_Agrotec

Thanks again, @Marc_Dillon , I took your advise. The whole topic arose with reporting “0” which after your feedback I decided to leave things how they where and solved it at the report level:

<<IF([pH (Pro)] = 0, “NA”, [pH (Pro)]) >>