Replicating SQL statement in Magic ETL Formula tool

ozarkram

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

mhouston

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

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

mhouston

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

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

ozarkram

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

jaeW_at_Onyx

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)