Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
The required output format is:
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:)