Replicating SQL statement in Magic ETL Formula tool

Hi All,

I am trying to replicate the following SQL statement in the MagicETL formula tool..


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!



Best Answers

  • mhouston
    mhouston 🟢
    Answer ✓

    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.

  • GrantSmith
    GrantSmith Indiana 🥷
    Answer ✓


    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!**


  • ozarkram
    ozarkram ⚪️

    Thank you @GrantSmith and @mhouston ..Really appreciate your help!

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🔴

    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"