Interval Match Help

[h_prakash]

Hello Experts,

Need your help in the below requirement,

I have two Tables

Project	Allocation Months	Hrs
1	Jan-18	10
1	Feb-18	20
1	Mar-18	30
1	Apr-18	40
1	May-18	50
1	Jun-18	60
1	Jul-18	70
1	Aug-18	80
1	Sep-18	90
1	Jan-19	10
1	Feb-19	30
1	Mar-19	50
1	Aug-19	52
1	Dec-19	25

Project	AllocationStart	Allocation End
1	Jan-18	Dec-18
1	Feb-19	Jun-19

I want the result to be like this in the End table. Is there any way we can do this in Back end.

Project	AllocationStart	Allocation End	Total Hours
1	Jan-18	Dec-18	450
1	Feb-19	Jun-19	80

[Quy_Nguyen · Accepted solution]

Try this:

Data:
LOAD
    Project,
    Date#("Allocation Months",'MMM-YY') As "Allocation Months",
    Hrs
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);

Interval:
LOAD
    Project,
    Date#("AllocationStart",'MMM-YY') As Start,
    Date#("Allocation End",'MMM-YY') As End
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @2);

_tmp:
NoConcatenate
Load *
Resident Data;
Left Join IntervalMatch ("Allocation Months", Project)
Load Start, End, Project 
Resident Interval;

Drop table Data, Interval;

Final:
Load Project, Start, End, Sum(Hrs) As TotalHours
Resident _tmp
Group by Project, Start, End;

Drop table _tmp;

Result:

If you don't want the project with out start and end (not in the interval table), just change the Left Join to Inner Join.

I also attached a demo app for you.

[h_prakash]

Thank you it worked...!!!