Interval Match

[NormanStanleyBadger]

Hello, I hope everyone is well. 😃

I have created a budget snapshot as per below.

Customer No	Location No	Product Category	Start Date	End Date	Budget Total	Non Sale Days	Network Days	Sale Day Apportionment
1	Area 1	Product 1	01/01/2024	31/01/2024	50	'02/01/2024','05/01/2024' etc	(Start Date,  End Date, Non Sale Days)	Budget Total / Network Days

The table repeats for 12 months for each Customer/Area/Product combo and for around 400 combinations.

My next goal is to create a budget table at the Date Level, but with only the Sale Days for each Customer/Area/Product and the matching Sale Day Apportionment.

I can obtain the Date Level table using interval match, but this gives me 365 days for each Customer/Area/Product matched against the Start Date & End Date. Given the Daily Apportionment is based on Sale Days only, then the numbers will be incorrect.

Therefore, I need to filter out the Non-Sale Days from the table. I can probably do this using a loop and the Non-Sale Days field but I was hoping there might be another way around? 

NSB

[marcus_sommer]

I suggest to apply a multi-step approach in which the date-range is fully expanded and afterwards filtered. It's easier and faster as methods which skip the excluded dates directly.

Similar to the intervalmatch but simpler and more flexible and powerful is the use of an internal while-loop and pulling the wanted information from a master-calendar. This may look like:

m: mapping load Date, [WorkDateFlag] & '|' & [WorkdaysPerMonth]
resident Calendar;

load *, [Budget Total] / subfield(applymap('m', Date, 0), '|', 2) as [Budget]
        where subfield(applymap('m', Date, 0), '|', 1);
load *, date([Start Date] + iterno() - 1) as Date
from YourSource while [Start Date] + iterno() - 1 <= [End Date];

whereby [WorkDateFlag] is just a 0/1 flag created with if-loops against weekday(Date) and another mapping of the holidays and [WorkdaysPerMonth] is an self-joined sum() aggregation of the [WorkDateFlag].