Compare and get the data based on Date using IntervalMatch function

ManimekalaiS — Sun, 25 Jun 2023 18:55:52 GMT

// ==== 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:

topic Compare and get the data based on Date using IntervalMatch function in App Development

Compare and get the data based on Date using IntervalMatch function