Skip to content

    • Beast Mode

    Beast Mode

    Assigning blank value as Zero

    Member
    edited March 2023 in Beast Mode

    Hi All,

    I have a field by the name rate which has decimal values and this field is coming in from a google sheet..

    I have a lot of rows of this field as blank ...Can you please let me know how to replace the blank values as 0..

    I am currently using this case when ifnull(rate,0) then 0 else rate....This is giving an error

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answers

    • Member
      Answer ✓

      CASE WHEN rate IS NULL THEN 0 ELSE rate END

    • Coach
      edited June 2022 Answer ✓ 
      1. COALESCE(`rate`, 0)

      Coalesce also works. It returns the first non-null value supplied. This is assuming your data is actually NULL and not an empty string. If it is you can force it to be null:

      1. COALESCE(NULLIF(TRIM(`rate`), ''), 0)

      TRIM to strip whitespaces at the beginning and end of the string for good measure.


      Also to address your beast mode IFNULL will return a value but it's not evaluating to TRUE for FALSE as the case statement expects. This is why you're getting an error.

      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**

    Answers

    • Member
      Answer ✓

      CASE WHEN rate IS NULL THEN 0 ELSE rate END

    • Coach
      edited June 2022 Answer ✓ 
      1. COALESCE(`rate`, 0)

      Coalesce also works. It returns the first non-null value supplied. This is assuming your data is actually NULL and not an empty string. If it is you can force it to be null:

      1. COALESCE(NULLIF(TRIM(`rate`), ''), 0)

      TRIM to strip whitespaces at the beginning and end of the string for good measure.


      Also to address your beast mode IFNULL will return a value but it's not evaluating to TRUE for FALSE as the case statement expects. This is why you're getting an error.

      **Was this post helpful? Click Agree or Like below**
      **Did this solve your problem? Accept it as a solution!**
    • Member

      Thank you so much @Recluz and @GrantSmith ..Really appreciate your help!

    • Member

      thank you it works in my case

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories