Load script flag if sum within date range greater than

[clifford]

Hello,

For each product I'm trying to sum that product type within +-5 days of the produce_date attached to that row, then create a flag if the summed amount is over 180. So the flag for key 1 would be false, 4 would be true, 6 would be true, and 8 would be false. I've been unable to find how to do this in the load script. Thank you.

key	produce_date	product	amount
1	1/1/2024	a	50
2	1/1/2024	b	300
3	1/1/2024	c	180
4	1/15/2024	a	150
5	1/15/2024	b	300
6	1/17/2024	a	100
7	1/17/2024	c	250
8	1/22/2024	a	50

[LRuCelver]

//	Setup: Load data and (optionally) define variables
Data:
NoConcatenate Load
	key,
	Date(Date#(produce_date, 'M/D/YYYY')) as produce_date,
	product,
	amount
Inline [
	key,	produce_date,	product,	amount
	1,		1/1/2024,		a,			50
	2,		1/1/2024,		b,			300
	3,		1/1/2024,		c,			180
	4,		1/15/2024,		a,			150
	5,		1/15/2024,		b,			300
	6,		1/17/2024,		a,			100
	7,		1/17/2024,		c,			250
	8,		1/22/2024,		a,			50
];

Let vIntervalDaysBefore = 5;
Let vIntervalDaysAfter = 5;
Let vFlagGreaterThanAmount = 180;


//	Create the intervals and join them to the keys
FlagData:
NoConcatenate Load Distinct
	key,
	produce_date,
	product
Resident Data;

Intervals:
NoConcatenate Load
	Date(produce_date - $(vIntervalDaysBefore)) as produce_date_start,
	Date(produce_date + $(vIntervalDaysAfter)) as produce_date_end,
	product,
	amount
Resident Data;

Left Join(FlagData) IntervalMatch(produce_date, product) Load Distinct
	produce_date_start,
	produce_date_end,
	product
Resident Intervals;

Left Join(FlagData) Load Distinct
	produce_date_start,
	produce_date_end,
	product,
	amount
Resident Intervals;

Drop Table Intervals;


//	Calculate the total amounts and set the flags
Join(Data) Load
	*,
	If(range_amount > $(vFlagGreaterThanAmount), 1, 0) as flag;
Load
	key,
	Sum(amount) as range_amount
Resident FlagData
Group By key;

Drop Table FlagData;