Load script flag if sum within date range greater than

clifford — Wed, 28 Feb 2024 15:32:18 GMT

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

Re: Load script flag if sum within date range greater than

LRuCelver — Wed, 28 Feb 2024 16:15:47 GMT

// 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;

topic Re: Load script flag if sum within date range greater than in App Development

Load script flag if sum within date range greater than

Re: Load script flag if sum within date range greater than