Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
// ==== Load Activities ==================================================
CreatedData:
LOAD * INLINE [SalDate, Key, Desc
01-04-2023, 0013A00001gWXDCQA4, 0013A00001gWXDCQA4||01/04/2023
15-05-2023, 0013A00001gWXDCQA4, 0013A00001gWXDCQA4||15/05/2023
23-05-2023, 0013A00001gWXDCQA4, 0013A00001gWXDCQA4||23/05/2023
24-05-2023, 0013A00001gWXDCQA4, 0013A00001gWXDCQA4||24/05/2023
31-05-2023, 0013A00001gWXDCQA4, 0013A00001gWXDCQA4||31/05/2023
06-06-2023, 0013A00001gWXDCQA4, 0013A00001gWXDCQA4||06/06/2023
01-06-2023, 0013A00001gWCQA5, 0013A00001gWCQA5||01/06/2023
26-06-2023, 0013A00001gWCQA5, 0013A00001gWCQA5||26/06/2023];
// ===== Load the Details ================================================
DetailData:
LOAD * INLINE [START_DATE, END_DATE, Key, Value
01-04-2023, 01-05-2023, 0013A00001gWXDCQA4, 1
15-05-2023, 15-06-2023, 0013A00001gWXDCQA4, 1
16-05-2023, 16-06-2023, 0013A00001gWXDCQA4, 1
23-05-2023, 23-06-2023, 0013A00001gWXDCQA4, 1
24-05-2023, 24-06-2023, 0013A00001gWXDCQA4, 1
31-05-2023, 31-06-2023, 0013A00001gWXDCQA4, 1
06-06-2023, 06-07-2023, 0013A00001gWXDCQA4, 1
01-06-2023, 01-07-2023, 0013A00001gWCQA5, 1
26-06-2023, 26-07-2023, 0013A00001gWCQA5, 1];
// ==== Link Fields from CreatedData with DetailData into the LinkWithInterval-Table ========
LinkWithInterval:
INTERVALMATCH(SalDate,Key) LOAD START_DATE, END_DATE,Key RESIDENT DetailData;
// ==== Join into one Table only, remove DetailData,CreatedData =====================
LEFT JOIN (CreatedData) LOAD distinct * RESIDENT DetailData;
DROP TABLE DetailData,CreatedData;
Output:
LOAD
Desc,
sum(Value) as Value
Resident LinkWithInterval
where Num(Date#(SalDate,'DD-MM-YYYY')) >= Num(Date#(START_DATE,'DD-MM-YYYY')) and
Num(Date#(SalDate,'DD-MM-YYYY')) <= Num(Date#(END_DATE,'DD-MM-YYYY')) group by Desc;
DROP TABLE LinkWithInterval;
exit Script;
Output Data Screenshot: