[Bug]: Error step formula IFNA

[RodrigoCossetin]

Apache Hop version?

2.7.0 (2023-11-17 12.19.09)

Java version?

java.version=11.0.16

Operating system

Windows

What happened?

Step formula IFNA does not recognize null fields to generate the calculation. I have a field called evaluation_date with null and filled values ​​I used the formula as follows: IFNA([evaluation_date], now()) The intention is when the value is null to bring today's date.

Issue Priority

Priority: 3

Issue Component

Component: Transforms

[RodrigoCossetin]

Hello, anything about this? Thanks...

[alexray92]

I'm also encountering this in 2.9. After merging two inputs as a full outer join, one with a CLOSE_DATE column and one with OPEN_DATE, I tried IFNA([OPEN_DATE],[CLOSE_DATE]) and IF(ISNA([OPEN_DATE]),[CLOSE_DATE],[OPEN_DATE]) to ensure an EVENT_DATE column is created. But in every case, if OPEN_DATE is null, EVENT_DATE becomes 1899.

I tried adding a Select step before to explicitly cast the metadata for OPEN_DATE and CLOSE_DATE to be Date type, but that didn't help either. So either this is a bug or we're both not understanding some nuance. The two columns have identical date-stamp formatting as well, I should add.

[alexray92]

I will add that, interestingly, NVL() in Calculator works fine.

[RodrigoCossetin]

Também estou encontrando isso no 2.9. Depois de mesclar duas entradas como uma junção externa completa, uma com uma coluna CLOSE_DATE e uma com OPEN_DATE, tentei IFNA([OPEN_DATE],[CLOSE_DATE]) e IF(ISNA([OPEN_DATE]),[CLOSE_DATE],[OPEN_DATE]) para garantir que uma coluna EVENT_DATE seja criada. Mas em todos os casos, se OPEN_DATE for nulo, EVENT_DATE se tornará 1899.

Tentei adicionar uma etapa Select antes para converter explicitamente os metadados para OPEN_DATE e CLOSE_DATE para o tipo Date, mas isso também não ajudou. Então, ou isso é um bug ou nós dois não estamos entendendo alguma nuance. As duas colunas têm formatação de carimbo de data idêntica também, devo acrescentar.

Exactly, it's probably a bug. This formula doesn't work. Will they notice this issue? Today version 10 of hop was released.

[tlue]

I think the formula transform seems to have some general issue. i.e. using the example from the hop docs I also get this error: https://hop.apache.org/manual/latest/pipeline/transforms/formula.html

REPLACE("123456789";5;3;"Q")

from my logging (with HOP 2.9): Formula.0 - ERROR: Unexpected error Formula.0 - ERROR: org.apache.hop.core.exception.HopException: Formula.0 - Formula 'REPLACE("123456789";5;3;"Q")' could not not be parsed Formula.0 - Parse error near char 19 ';' in specified formula 'REPLACE("123456789";5;3;"Q")'. Expected ',' or ')'

This Formula works (use "," instead of ";"): REPLACE("123456789",5,3,"Q")

[bamaer]

@tlue the docs (incorrectly) mention semicolons (;) as formula separators. this should be comma (,) as specified in the in-transform docs and samples.

[hansva]

Updated the docs

[hansva]

.take-issue

[hansva]

This issue boils down to formula knowledge and assumptions: The IFNA() function requires a cell or result of a formula to be #N/A. When we encounter a null value in an input field we set the cell to a blank cell, not to #N/A.

When executing IFNA([BLANK_CELL],"result") it will return Blank Cell POI Docs for more information on that. and an Example from excel:

Executing following formula works IFNA(NA(),"result").

The correct check to use in this case is to use ISBLANK() to check if there is no value.

I will add an extra option to the dialog to set empty input fields to NA this will be done by injecting the NA() formula in the cell as it seems there is no way to set a cell to #N/A