Coalesce function doesn't work properly in prepare recipe

[kentnardGaleria]

Hi everyone!

I have a question regarding the coalesce recipe in dataiku. I wanted to use the coalesce funtion in dataiku Formula and the preview that I have in the prepare recipe shows that the function works and it shows the value that I want. But after executing the recipe, the resulting column shows a different output from the preview. 

I have made sure that the order of the values in the coalesce function is correct and that the empty cells are NULL instead of an empty string. I could not comprehend where the mistake is. The pictures are attached below. Picture 1 shows the preview in prepare recipe and Picture 2 shows the resulting dataset.

Thanks in advance!

[JordanB]

Hi @kentnardGaleria,

I was not able to reproduce the issue on my end. Just to confirm, did you select "APPLY" on the upper right hand of the screen on the formula processor? What version of DSS are you using? 

We would likely need to take a look at the job diagnostic in order to identify the cause of this. Please feel free to open a support ticket with Dataiku. 

Kind Regards,

Jordan

[kentnardGaleria]

Hello Jordan, 

thank you for the reply. I can confirm that I have already selected "APPLY" in the prepare recipe before running it. The DSS version that I am currently using is 11.3.1. 

Kind regards,

Kentnard

[JordanB]

Hi @kentnardGaleria,

Can you test this process running on the DSS engine instead of SQL and let us know if that changes the output?

Thanks,

Jordan

[kentnardGaleria]

Hi Jordan, 

I've tried running on the DSS Engine and it works properly, it gives me the output that is shown in the preview. Moreover, when I chose to run the recipe on SQL, I checked the SQL query and it seems that the query would deliver the right output. The screenshot of the query is attached. 

Kind regards,

Kentnard

[Rickh008]

I have the same issue. Many results are blank when the first column is most certainly not blank.

ETA: Alright, I've decided that COALESCE() is simply riddled with errors when working with text. It seems to be fine with numeric values.

I've replaced the below formula code

coalesce(val("SERV_LEN_GRP_2"), val("SERV_LEN_GRP"))

with this code

if(isNonBlank(val("SERV_LEN_GRP_2")), val("SERV_LEN_GRP_2"), val("SERV_LEN_GRP"))

and that has solved my issue.