Extend Magic FILTER to use a value calculated from another table

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)
   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

1  Copy of DF   DCM iProspect  14 Day Rolling   Historical   Domo.png


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.

0 votes

· Last Updated

This discussion has been closed.