nested case for arithmetic operators
Hi,
I've been having troubles with MySQL dataflow trying to write a code basically doing something like this:
(CASE WHEN week = 1 THEN (a/b) END) - (c/d) * e
i tried writing it down like this:
(CASE WHEN `Week` = "1" THEN SUM((CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN(`8Q_Price`*`Tons`) END))
/ SUM((CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END)) END) ----- (CASE when week = 1 THEN (a/b) END) ------
-
(SUM((CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN(`8Q_Price`*`Tons`) END))
/ SUM((CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END))) ----(c/d)----
*
SUM((CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END)) ----e-----
I am not having syntax errors with this code but I keep on getting wrong answers even if I change the groupings. Can you guys recommend something?
Thanks!
p.s. a colleague of mine recommended to use a CREATE FUNCTION statement, will it work in our MySQL Dataflow?
Best Answer
-
Hey drewfig,
No worries. I moved some of the parentheses around so this should work for what you described above.
((SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" and `Week` = "1" THEN `8Q_Price`*`Tons` END)
/
SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" and `Week` = "1" THEN `Tons` END)) ----- (CASE when week = 1 THEN (a/b) END) ------
-
(SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `8Q_Price`*`Tons` END)
/ SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END))) ----(c/d)----
*
SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END) ----e-Hope this helps,
Brian
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.0
Answers
-
Hello Drewfig,
Based on the order of operations, you want your algorithm to divide a and b then divide c and d then multiply the results of c/d by e and then subtract the results of (a/b) and (c/d*e).
(SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" and `Week` = "1" THEN `8Q_Price`*`Tons` END)
/
SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" and `Week` = "1" THEN `Tons` END)) ----- (CASE when week = 1 THEN (a/b) END) ------
-
((SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `8Q_Price`*`Tons` END)
/ SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END)) ----(c/d)----
*
SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END)) ----e-The above will work for what you described in your post.
Hope this helps,
Brian
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.1 -
Hey Brian,
Thanks for taking time to check on my question. actually what we wanted to do is to subtract the results of (a/b) and (c/d) and then the result of the subtraction will be multiplied by e.
It will be like ((a/b) - (c/d)) * e.
I apologize for the confusion yesterday. can you help revising the code? thanks!
0 -
Hey drewfig,
No worries. I moved some of the parentheses around so this should work for what you described above.
((SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" and `Week` = "1" THEN `8Q_Price`*`Tons` END)
/
SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" and `Week` = "1" THEN `Tons` END)) ----- (CASE when week = 1 THEN (a/b) END) ------
-
(SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `8Q_Price`*`Tons` END)
/ SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END))) ----(c/d)----
*
SUM(CASE WHEN `8Q` = "POR" AND `Fiscal_Year` = "FY19" THEN `Tons` END) ----e-Hope this helps,
Brian
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.0
Categories
- All Categories
- 1.1K Product Ideas
- 1.1K Ideas Exchange
- 1.2K Connect
- 969 Connectors
- 256 Workbench
- Cloud Amplifier
- 1 Federated
- 2.4K Transform
- 76 SQL DataFlows
- 501 Datasets
- 1.8K Magic ETL
- 2.7K Visualize
- 2.2K Charting
- 375 Beast Mode
- 20 Variables
- 484 Automate
- 102 Apps
- 378 APIs & Domo Developer
- 6 Workflows
- 22 Predict
- 6 Jupyter Workspaces
- 16 R & Python Tiles
- 316 Distribute
- 64 Domo Everywhere
- 252 Scheduled Reports
- 59 Manage
- 59 Governance & Security
- 1 Product Release Questions
- 5K Community Forums
- 37 Getting Started
- 23 Community Member Introductions
- 63 Community Announcements
- 4.8K Archive