Is Magic ETL 2.0 able to handle more complex SQL injections?

Pierce13
Pierce13 Member
in Magic ETL

Alright guys, I feel like I am going crazy. I have a beast mode card that works really well in calculating months on hand inventory. I am now trying to create an new ETL that does a group by vendor and I am trying to inject the same code. The issue is that 'Available Inventory' is always returning null for some reason. Is this a bug with Domo? Or is there an issue with my code?

CASE 

when Sum(CASE when `Type`='Sales' then `Value` else 0 end) = 0 then 0

ELSE

Sum(CASE when `Type`='Available Inventory' then `Value` else 0 end)

/

  Sum(CASE when `Type`='Sales' then `Value` else 0 end)

END

The result I am getting is:


image.png

I have even tried simply filtering Type = 'Available Inventory' and nothing is returning. The odd part is that the only Type that is found is 'Sales'


Any advice would be great. Thanks!

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    @Pierce13 when you say Available Inventory is returning nothing, is that through the preview tabs when you are creating the ETL? Or is it after you Save & Run the ETL and the ETL fully runs? The preview window can be a little misleading when testing since it doesn't ingest all of your data if you have a large dataset and the preview window will only show you the first 100 rows.

    Typically, if you have it working in a beast mode, you should be able to copy and paste it to the formula tile in Magic ETL.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Pierce13
    Pierce13 Member

    I did just run it and the filter is working, but the group by still is not. When I run the ETL, the MOH - Available column that I create is returning all 0's.

  • MarkSnodgrass
    MarkSnodgrass Coach

    Ahh... I understand a little better what is going on. In Magic ETL, you will want to use the Group By tile and then choose Add Formula and try pasting the formula into that.

    If that doesn't work, I would try breaking things out into separate steps where your formula tile creates the individual components without any summing and then use the group by to sum it up and then potentially another formula tile to do the division.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Coach

    I'd recommend using a formula tile to input your CASE statements which are inside your SUM aggregation as two separate values. Then do a group by to get the SUM then another formula tile to do the outer portion of the beast mode (checking if your denominator is 0)

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

    That seemed to work. Thanks so much guys!

Categories