Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have date information in two separate tables and i want to find if a date from table1.date exists in between date ranges of table 2 (date1.table2 and date2.table2). on the basis of the comparison i want to add a comment.
TABLE1:
EmployeeID | absencedate |
---|---|
0001 | 01.01.2013 |
0002 | 02.02.2012 |
0003 | 03.03.2012 |
TABLE2:
EmployeeID | LeaveFrom | LeaveTo |
---|---|---|
0001 | 12.12.2012 | 25.12.2012 |
0002 | 01.02.2012 | 05.02.2012 |
0003 | 03.03.2012 | 03.03.2012 |
RESULT TABLE should be like this:
EMPLOYEEID | ABSENCEDATE | Comments |
---|---|---|
001 | 01.01.2013 | not found |
0002 | 02.02.2012 | found |
0003 | 03.03.2012 | found |
it would be great if i can achieve it through scripting.
Hi,
U can create a calulated dimension like below
=if(IsNull(Start) and IsNull(End),'Not Found','Found')
PFA,
Regards,
Kabilan K
Hi, try this.
TABLE2:
LOAD * INLINE [
EmployeeID, LeaveFrom, LeaveTo
0001, 12.12.2012, 25.12.2012
0002, 01.02.2012, 05.02.2012
0003, 03.03.2012, 03.03.2012
];
TABLE1:
LOAD * INLINE [
EmployeeID, absencedate
0001, 01.01.2013
0002, 02.02.2012
0003, 03.03.2012
];
LEFT JOIN
IntervalMatch(absencedate)
LOAD
LeaveFrom,
LeaveTo
RESIDENT TABLE2;
DROP Table TABLE2;
TABLE3:
LOAD *,
If(IsNull(LeaveFrom)=-1, 'not found', 'found') as Comment
RESIDENT TABLE1;
DROP TABLE TABLE1;
Regards, Sander
Hi,
PFA,
Regards,
Kabilan K.
Thanks Sander
Leave From and Leave To are empty by ur script hence only "not found" is appearing in "Comment"
Message was edited by: sidrahasan
Thanks Kabilan
Your solution works perfectly but with large data i am having a problem.
The "found" details are perfectly right but it is again appearing with "Not Found" comment as well.
Hi,
I didn't get u clearly so explain pls explain with sample.
there may be lots of employees absent on the same date so i have multiple employeeids for an absence date.
You may understand via this screenshot
You can see the date"08-07-2011' exists in LeaveFrom so ideally it should have comment only"Found" but it also gives status "Not Found" too.
Please find the sample data attached.
EmployeeID | From | To |
3673 | 05-01-2012 | 06-01-2012 |
3673 | 13-01-2012 | 13-01-2012 |
3673 | 29-03-2012 | 29-03-2012 |
3673 | 04-06-2012 | 15-06-2012 |
3673 | 13-07-2012 | 13-07-2012 |
3673 | 26-07-2012 | 26-07-2012 |
EmployeeID | AbsenceDate |
00003673 | 01-01-2012 |
00003673 | 05-01-2012 |
00003673 | 06-01-2012 |
00003673 | 29-03-2012 |
00003673 | 09-04-2012 |
00003673 | 21-05-2012 |
00003673 | 01-06-2012 |
00003673 | 04-06-2012 |
00003673 | 05-06-2012 |
00003673 | 06-06-2012 |
00003673 | 07-06-2012 |
00003673 | 08-06-2012 |
00003673 | 11-06-2012 |
00003673 | 12-06-2012 |
00003673 | 13-06-2012 |
00003673 | 14-06-2012 |
00003673 | 15-06-2012 |
00003673 | 18-06-2012 |
00003673 | 27-06-2012 |
00003673 | 28-06-2012 |
00003673 | 29-06-2012 |
00003673 | 02-07-2012 |
00003673 | 03-07-2012 |
00003673 | 06-07-2012 |
00003673 | 26-07-2012 |
00003673 | 13-08-2012 |
00003673 | 16-08-2012 |
00003673 | 20-08-2012 |
00003673 | 21-08-2012 |
00003673 | 22-08-2012 |
00003673 | 21-09-2012 |
00003673 | 13-11-2012 |
00003673 | 14-11-2012 |
00003673 | 24-12-2012 |
00003673 | 25-12-2012 |
00003673 | 26-12-2012 |
Did u find the reason Y is it show like that?
I guess that LeaveTo has Null value for that employee No.
If it is so pls Let me know
~Kabilan K.