Filtering and Adjusting Cross-Month Records in MagicETL

DataLawton

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:

1. Extract Month and Year: Use a Date Operations tile to pull out the month and year from both RSD and RED.
2. Create a Formula: Use a Formula tile to flag records where RSD and RED differ in month or year.
3. 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!

rco

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