Replicating SQL statement in Magic ETL Formula tool

ozarkram
ozarkram Member
in Magic ETL

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

Tagged:

Best Answers

  • mhouston
    mhouston Contributor
    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 Coach
    Answer ✓

    @ozarkram

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

Answers

  • mhouston
    mhouston Contributor
    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 Coach
    Answer ✓

    @ozarkram

    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 Member

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

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    you can write formulas in the GROUP BY tile too.

    image.png


    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"

Categories