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
- Get the last 14 days and save this as "DCM rolling 14 days".
- Load the existing "DCM history" file.
- Filter "DCM history" where Date < MIN(date) in "DCM rolling 14 days".
- Append 3 with the "DCM rolling 14 days".
- 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
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.
- 10.7K All Categories
- 1 APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 339 Workbench
- 252 Domo Best Practices
- 11 Domo Certification
- 461 Domo Developer
- 47 Domo Everywhere
- 101 Apps
- 705 New to Domo
- 84 Dojo
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 23 お知らせ
- 63 Kowaza
- 297 仲間に相談
- 649 ひらめき共有