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

Pierce13 · 2021-12-09T19:19:08+00:00

There was an error rendering this rich post.

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:

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!

Find more posts tagged with

Accepted answers

All comments

MarkSnodgrass

@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.

Pierce13

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

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.

GrantSmith

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)

Pierce13

That seemed to work. Thanks so much guys!