Coalesce function doesn't work properly in prepare recipe

Options
kentnardGaleria
kentnardGaleria Registered Posts: 13

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!

Answers

  • JordanB
    JordanB Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 293 Dataiker
    Options

    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
    kentnardGaleria Registered Posts: 13
    Options

    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
    JordanB Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 293 Dataiker
    Options

    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
    kentnardGaleria Registered Posts: 13
    Options

    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
    Rickh008 Dataiku DSS Core Designer, Registered Posts: 15 ✭✭✭✭
    Options

    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.

Setup Info
    Tags
      Help me…