Using Beast Mode to substitute null fields

jlazerus
jlazerus Member
in Magic ETL

Is there a way to use Beast Mode to make the null entries 0's in the attached picture?Screen Shot 2017-10-10 at 7.18.21 PM.png

Best Answer

  • RGranada
    RGranada Contributor
    Answer ✓

    Let's make some modifications on the beast modes:

     

    Resolved:

    SUM(CASE WHEN `Status`='Resolved' THEN 1 ELSE 0 END)

     

    Open:

    SUM(CASE WHEN `Status`='Resolved' THEN 1 ELSE 0 END)

     

    Don't mind the SUM() as it is just another way of counting the cases.Apply these ones with no aggregation. I did a graf by week and the results seem similar to yours:


    2017-10-11_160158.png

     

    Hope this helps, let me know if it works.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.

Answers

  • RGranada
    RGranada Contributor

    Hi,

     

    You can use on a beast mode :

     

    IFNULL(YOUR_FIELD, 0)

     

    Hope this helps. 

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • jlazerus
    jlazerus Member

    Hi Ricardo,

     

    I actually tried this on two of my fields but it doesn't seem to change anything on the chart.  I tried it on the field being counted in the Y axis and also tried it in the Series.

  • RGranada
    RGranada Contributor

    I see...

     

    I think that is because you are lacking dates in your x-axis.

    Can you send a table view of your data?

     

    Meanwhile, I'll think of something to address this if it's the case.

     

    Regards

     

     

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • jlazerus
    jlazerus Member

    Table view attached.  

  • RGranada
    RGranada Contributor

    Ok,

     

    I'm a little confused here. Does Case Number represent the number of cases that were open or solved in a given Date. What is the goal of the card?

     

    If you could enlighten me a little more in order to give you the best answer.

     

    Regards,

     

     

     

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • jlazerus
    jlazerus Member

    I'm sorry if I wasn't clear.  What I'm doing on the card is counting the number of `Case Number` and using `Status` as the series.  This shows me how many cases were opened each week and of those, which ones are still open and which ones are resolved.  What I'm looking to do is to show that '0' are still open for the gaps in the chart.  

  • RGranada
    RGranada Contributor

    Ok,

     

    So, if I correctly understood the card goal, give this a go:

     

    Create 2 Beast Modes like this :

     

    BeastMode OPEN:

    CASE WHEN `Status`='Open' THEN `Case Number` ELSE 0 END

     

    BeastMode Resolved:

    CASE WHEN `Status`='Resolved' THEN `Case Number` ELSE 0 END

     

    Then apply them to your card this way:

     

    2017-10-11_151735.png

     

    I think this will give you the expected results.

     

    Hope this helps, don't hesitate to ask if you need more help on this.

     

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • jlazerus
    jlazerus Member

    So I just tried this and SUM is not an option when I do this.  I can either COUNT or use No Aggregation.  When I applied this with COUNT, it looks like it combines to two values (open, resolved) into the same line on the chart.

  • RGranada
    RGranada Contributor

    Ok,

     

    Can you share a sample of the dataset you are getting the data from?

    I wrongly thought that the table you sent was it, but it is just the table view of the card.

     

    Regards

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • jlazerus
    jlazerus Member

    Sure thing. 

  • RGranada
    RGranada Contributor
    Answer ✓

    Let's make some modifications on the beast modes:

     

    Resolved:

    SUM(CASE WHEN `Status`='Resolved' THEN 1 ELSE 0 END)

     

    Open:

    SUM(CASE WHEN `Status`='Resolved' THEN 1 ELSE 0 END)

     

    Don't mind the SUM() as it is just another way of counting the cases.Apply these ones with no aggregation. I did a graf by week and the results seem similar to yours:


    2017-10-11_160158.png

     

    Hope this helps, let me know if it works.

    Ricardo Granada 

    MajorDomo@Lusiaves

    **If the post solves your problem, mark it by clicking on "Accept as Solution"
    **You can say "Thank you" by clicking the thumbs up in the post that helped you.
  • jlazerus
    jlazerus Member

    That did it! Thanks so much!!!!!

Categories