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.
LeaveTo is not null
EmployeeID | LeaveFrom | LeaveTo |
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 |
Hi, We used a join function know that a Y we face like this,,
We have to find alternative way to finish this..
Thanks Kabilan
The alternative i think could be IntervalMatch,,, but again its not working for me.. Can u see please this code.. y this is not working??
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;
hello Sidrahasan,
Try this code.
test:
LOAD EmployeeID,
num(date(date#(absencedate,'DD.MM.YYYY'),'MMDDYYYY')) as absencedate
FROM
[http://community.qlik.com/message/296652#296652]
(html, codepage is 1252, embedded labels, table is @1);
left join
LOAD EmployeeID,
num(date(date#(LeaveFrom,'DD.MM.YYYY'),'MMDDYYYY')) as leavefrom ,
num(date(date#(LeaveTo,'DD.MM.YYYY'),'MMDDYYYY')) as leaveto
FROM
[http://community.qlik.com/message/296652#296652]
(html, codepage is 1252, embedded labels, table is @2);
test1:
load EmployeeID,
absencedate,
leavefrom,
leaveto,
if(absencedate >= leavefrom and absencedate <= leaveto,'found','notfound') as comment
Resident test;
drop table test;
Pls reply.
Thanks nazneen for the reply.
Your code is same as Kbilan mentioned above and its not working as its joining one absence date to all leaveTOo and LeaveFrom date hence producing both the Result for a record.
Hi,
U can create a calulated dimension like below
=if(IsNull(Start) and IsNull(End),'Not Found','Found')
PFA,
Regards,
Kabilan K