Replicating SQL statement in Magic ETL Formula tool
Hi All,
I am trying to replicate the following SQL statement in the MagicETL formula tool..
(SELECT MAX("FISCAL_YEAR") FROM table WHERE "DATE_VALUE" = CURRENT_DATE) AS CUR_FISCAL_YEAR
In the Magic ETL tool I am giving the name of the output column as CUR_FISCAL_YEAR and Write Formula as MAX(`FISCAL_YEAR`) WHERE `DATE_VALUE`=CURDATE()
I am getting the following error
Syntax error in expression at character 19
Can you please help!
Thanks,
Arun
Best Answers
-
Does your dataset include future fiscal years so that you need the where clause for "DATE_VALUE" = current_date? if not you could just use a group by tile (you may need to add a constant tile before hand with a dummy constant to group on) and get max(fiscal_year).
Otherwise you could use the filter tile to filter where date_value = current_date, and then do the group by tile to get the max. Then you can join back to your dataset.
2 -
You can't do aggregation or filtering in a formula tile. It's only simple calculations or functions are allowed.
As @mhouston stated, use a filter tile to filter your dataset to get the current date, and then do a group by tile to get the max fiscal date.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Answers
-
Does your dataset include future fiscal years so that you need the where clause for "DATE_VALUE" = current_date? if not you could just use a group by tile (you may need to add a constant tile before hand with a dummy constant to group on) and get max(fiscal_year).
Otherwise you could use the filter tile to filter where date_value = current_date, and then do the group by tile to get the max. Then you can join back to your dataset.
2 -
You can't do aggregation or filtering in a formula tile. It's only simple calculations or functions are allowed.
As @mhouston stated, use a filter tile to filter your dataset to get the current date, and then do a group by tile to get the max fiscal date.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 -
Thank you @GrantSmith and @mhouston ..Really appreciate your help!
0 -
you can write formulas in the GROUP BY tile too.
but as @GrantSmith pointed out, a Formula tile cannot have any aggregations (MAX, SUM, COUNT)
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 692 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive