Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
h_prakash
Creator II
Creator II

Interval Match Help

Hello Experts,

Need your help in the below requirement,

I have two Tables

ProjectAllocation MonthsHrs
1Jan-1810
1Feb-1820
1Mar-1830
1Apr-1840
1May-1850
1Jun-1860
1Jul-1870
1Aug-1880
1Sep-1890
1Jan-1910
1Feb-1930
1Mar-1950
1Aug-1952
1Dec-1925

 

ProjectAllocationStartAllocation End
1Jan-18Dec-18
1Feb-19Jun-19

 

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

ProjectAllocationStartAllocation EndTotal Hours
1Jan-18Dec-18450
1Feb-19Jun-1980
Labels (2)
1 Solution

Accepted Solutions
Quy_Nguyen
Specialist
Specialist

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:

clipboard_image_0.png

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.

View solution in original post

2 Replies
Quy_Nguyen
Specialist
Specialist

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:

clipboard_image_0.png

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
Creator II
Creator II
Author

Thank you it worked...!!!