findout date exists in an interval

[#1]

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.

[#2]

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

[#3]

Hi, We used a join function know that a Y we face like this,,

We have to find  alternative way to finish this..

[#4]

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;

[#5]

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.

[#6]

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.

[#7 · Accepted solution]

Hi,

U can create a calulated dimension like below

=if(IsNull(Start) and IsNull(End),'Not Found','Found')

PFA,

Regards,

Kabilan K