Case when SYNTAX

damen
damen Contributor
edited September 2023 in Magic ETL

Hi all,

I am trying to write a case statement for a column when we see a null to grab another column. I am not sure why I am not getting the output expected.

Can someone take a look and maybe advise?

image.png
image.png

Essentially 36534370 and 36534369 from the investor_loan_id column should be in the left column as well.

**I've tried to change both columns to integers and back, that wasnt the issue

If this helps, feel free to agree, accept or awesome it!

Tagged:

Best Answer

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Did you try skipping the transform to null?

    Case when coalesce(trim(`commitment`), '') ='' then `loan` else `commitment` end

    I can't find this written anywhere, so maybe I'm making it up, but I'm pretty sure you can't THEN NULL to return nulls. I'm surprised it's not throwing a syntax error.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

Answers

  • DavidChurchman
    DavidChurchman Coach
    Answer ✓

    Did you try skipping the transform to null?

    Case when coalesce(trim(`commitment`), '') ='' then `loan` else `commitment` end

    I can't find this written anywhere, so maybe I'm making it up, but I'm pretty sure you can't THEN NULL to return nulls. I'm surprised it's not throwing a syntax error.

    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • damen
    damen Contributor

    Ah, beautiful. Thank you!

    If this helps, feel free to agree, accept or awesome it!

Categories