You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@hop.apache.org by "RodrigoCossetin (via GitHub)" <gi...@apache.org> on 2024/04/03 15:24:42 UTC
[I] [Bug]: Error step formula IFNA (hop)
RodrigoCossetin opened a new issue, #3777:
URL: https://github.com/apache/hop/issues/3777
### 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
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Feature Request]: Error step formula IFNA (hop)
Posted by "hansva (via GitHub)" <gi...@apache.org>.
hansva closed issue #3777: [Feature Request]: Error step formula IFNA
URL: https://github.com/apache/hop/issues/3777
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Error step formula IFNA (hop)
Posted by "alexray92 (via GitHub)" <gi...@apache.org>.
alexray92 commented on issue #3777:
URL: https://github.com/apache/hop/issues/3777#issuecomment-2330239774
I'm also encountering this. 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.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Error step formula IFNA (hop)
Posted by "alexray92 (via GitHub)" <gi...@apache.org>.
alexray92 commented on issue #3777:
URL: https://github.com/apache/hop/issues/3777#issuecomment-2330264654
I will add that, interestingly, NVL() in Calculator works fine.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Error step formula IFNA (hop)
Posted by "hansva (via GitHub)" <gi...@apache.org>.
hansva commented on issue #3777:
URL: https://github.com/apache/hop/issues/3777#issuecomment-2462511114
Updated the docs
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Error step formula IFNA (hop)
Posted by "hansva (via GitHub)" <gi...@apache.org>.
hansva commented on issue #3777:
URL: https://github.com/apache/hop/issues/3777#issuecomment-2464290371
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](https://poi.apache.org/apidocs/dev/org/apache/poi/ss/formula/atp/IfNa.html) 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`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Error step formula IFNA (hop)
Posted by "tlue (via GitHub)" <gi...@apache.org>.
tlue commented on issue #3777:
URL: https://github.com/apache/hop/issues/3777#issuecomment-2460434187
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:
`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 is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Error step formula IFNA (hop)
Posted by "hansva (via GitHub)" <gi...@apache.org>.
hansva commented on issue #3777:
URL: https://github.com/apache/hop/issues/3777#issuecomment-2464165724
.take-issue
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Error step formula IFNA (hop)
Posted by "bamaer (via GitHub)" <gi...@apache.org>.
bamaer commented on issue #3777:
URL: https://github.com/apache/hop/issues/3777#issuecomment-2462396962
@tlue the docs (incorrectly) mention semicolons (`;`) as formula separators. this should be comma (`,`) as specified in the in-transform docs and samples.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] [Bug]: Error step formula IFNA (hop)
Posted by "RodrigoCossetin (via GitHub)" <gi...@apache.org>.
RodrigoCossetin commented on issue #3777:
URL: https://github.com/apache/hop/issues/3777#issuecomment-2206485571
Hello, anything about this? Thanks...
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@hop.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org