Holidays between two dates

KevinNorman

Dataset A contains date_start and date_end.

Dataset B is a calendar with a field called is_company_holiday.

I need to figure out a way (in Magic ETL 2.0) to create a value in Dataset A that tells me the number of holidays between date_start and date_end.

Anyone got any good suggestions?

Thanks to previous threads that have helped me get this far. @MarkSnodgrass in particular has been helpful. Thank you.

GrantSmith

Since you can't do conditional joins within Magic ETL 2.0 what you can do is add a constant to both datasets in your ETL and call it Join Column with a value of 1. Then do a join on both datasets on the join column. Then feed that into a filter tile to filter where company_holiday_date is BETWEEN your date_start and date_end. Then you can do a group by and count the number of records you have, joining that metric back to your original dataset A based on the start and end dates to record the number of holidays between the two dates.

KevinNorman

@GrantSmith Thank you for this quick resolution.

This makes complete sense, thank you for helping me get the last little bit of it.

I could see how, if Dataset A had large amounts of historical in it, the join in Magic could produce 100s of millions of rows fairly easily, lengthening your data run time. However, Adrenaline is pretty awesome and our historical only goes back a few months (for now). So our run time only increased by 1.5min.

Anyway, problem solved and thank you again!