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: 
Mapuna
Partner - Contributor III
Partner - Contributor III

Interval Match

I have been given 2 tables. Table 1 has emp id, dept and Joining date. Table 2 again has emp id, dept and dates of contiguous months when their salaries will be credited. Table 1 shows that the emp id 1 changes the  dept from a to f on a specific date. So he has to get the salary only upto that date from dept a and dept f should give the rest of the salaries.The 2 tables look like this: Table a and b:

AnupamRaj_0-1663743235640.png

The required output format is:

AnupamRaj_1-1663743264160.png

I have so far managed to take the cross table of table b and i have created a range of start date and end dates. I have to interval match the dates from b to these range of dates and obtain their salary. How can it be achieved?

The script so far:

a:
load * Inline [
id,dep,startdate
1, a, 1/1/2022
2, b, 1/1/2022
3, c, 1/1/2022
4, d, 1/1/2022
5, e, 1/1/2022
1, f, 1/2/2022
];


b:
CrossTable(enddate,salaries,2)load * Inline [

id,depp,9/1/2022, 9/2/2022,9/3/2022,9/4/2022
1, a, 10,20,30,40
2, b, 10,20,30,40
3, c, 10,20,30,40
4, d, 10,20,30,40
5, e, 10,20,30,40

];

NoConcatenate
c:
load id,dep as dept,startdate as startdatee ,
if(id=previous(id),peek('startdatee'),date#(date(today()),'dd/mm/yyyy')) as end
resident a order by id,startdate desc;

//The below code is not working and im not sure why
//inner join
// IntervalMatch(enddate)
// load startdatee,end resident c;

 

Kindly look into this:)

 

Labels (5)
0 Replies