Replace Undefined/Blank with a value in Beast Mode

Options
Prajju
Prajju Member
in Magic ETL

Hi,

 

 

I am calculating accuracy with a formula inside Beast Mode:

(A - F) / A * 100.

 

Capture1.PNG

 

There are few instances where the denominator is 0. 

For those instances, I am getting blank values as shown below:

 

Capture2.PNG

 

I want to replace the blank/undefined with a different value 

I tried to use the CASE and IFNULL BeastMode functions but that did not work.

Can someone help me with this?

 

Thanks,

Prajwal

Best Answer

  • AS
    AS Coach
    Answer ✓
    Options

    Here's what I've done in the past that has worked.  Call out the denominator as zero calulcation first, then go on to the calculation if it's fine.

     

    CASE

    WHEN IFNULL(SUM(`AdjustedSalesRevenue),0) = 0 THEN 0

    ELSE

    [calculation]

    END

     

    Does something like that work for you?

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Answers

  • AS
    AS Coach
    Answer ✓
    Options

    Here's what I've done in the past that has worked.  Call out the denominator as zero calulcation first, then go on to the calculation if it's fine.

     

    CASE

    WHEN IFNULL(SUM(`AdjustedSalesRevenue),0) = 0 THEN 0

    ELSE

    [calculation]

    END

     

    Does something like that work for you?

    Aaron
    MajorDomo @ Merit Medical

    **Say "Thanks" by clicking the heart in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • Prajju
    Prajju Member
    Options

    This works.Thanks.

     

    Prajwal

Categories