Extend Magic FILTER to use a value calculated from another table

doc-domo

I'm creating a Magic ETL to fix DCM "attribution window". The algorithm is 

1. Get the last 14 days and save this as "DCM rolling 14 days".
2. Load the existing "DCM history" file.
3. Filter "DCM history" where Date <  MIN(date) in "DCM rolling 14 days".
4. Append 3 with the "DCM rolling 14 days".
5. Write this out as the "DCM history" file.

  In SQL this can be easily written as 

    SELECT * FROM prod_dcm
    WHERE Date < (SELECT MIN(Date) FROM dcm_14_days_rolling LIMIT 1)
    UNION ALL
   SELECT * FROM dcm_14_days_rolling

 

 

This is difficult in Magic as Filter can't use a calculated value from another table.  The Magic implementation typically requires the use of a groupby and join and looks like this

 

The difference in performance is about 2x.

 

I'd like to see FILTER extended so it can use a value calculated from another table so I can easily write the attribution window transformation and have ok performance.