Filtering and Adjusting Cross-Month Records in MagicETL
Hey Domo Dojo Community,
I'm working with a dataset containing reservation start (RSD
) and end (RED
) dates in the format of MM/DD/YYYY
. I need to identify all records where the reservation spans across two different months (i.e., RSD
and RED
fall in different months or years). My objective is to filter out these reservation records, apply some adjustments, and then add them back into the dataset.
Here's my current approach:
- Extract Month and Year: Use a Date Operations tile to pull out the month and year from both
RSD
andRED
. - Create a Formula: Use a Formula tile to flag records where
RSD
andRED
differ in month or year. - Filter Rows: Filter out flagged records to adjust them as needed, and then add them back into the dataset afterward.
Are there any alternative or more efficient ways to handle this kind of date filtering and reintegration in MagicETL? Or perhaps a simpler solution that requires fewer tiles? I’d appreciate any insights or tips on managing date-based filtering and adjustments.
Thanks in advance!
Answers
-
Unless the adjustments you need to make to those cross-month reservations requires aggregates (e.g. Group By or Rank & Window tiles), then I would suggest not filtering them out and adding them back, as you say. Instead, do everything in a single Add Formula tile.
First formula:
Name: CROSSMONTH, Formula:MONTH(RSD) <> MONTH(RED) OR YEAR(RSD) <> YEAR(RED)
CROSSMONTH is now a boolean column that is true when the reservation start and end date months are not equal and false otherwise. In the same formula tile, we can now create additional columns or restate existing columns conditionally on CROSSMONTH using case statements. Subsequent formulas will look like this:
Name: EXAMPLE, Formula:CASE WHEN CROSSMONTH THEN EXAMPLE + 1 ELSE EXAMPLE END
Randall Oveson <randall.oveson@domo.com>
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 744 Beast Mode
- 58 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive