Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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.
Thank you it worked...!!!