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: 
Not applicable

findout date exists in an interval

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:

EmployeeIDabsencedate
000101.01.2013
000202.02.2012
000303.03.2012

TABLE2:

EmployeeIDLeaveFrom
LeaveTo
000112.12.201225.12.2012
000201.02.201205.02.2012
000303.03.201203.03.2012

RESULT TABLE should be like this:

EMPLOYEEIDABSENCEDATEComments
00101.01.2013not found
000202.02.2012found
000303.03.2012found

it would be great if i can achieve it through scripting.

1 Solution

Accepted Solutions
Not applicable
Author

Hi,

U can create a calulated dimension like below

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

PFA,

Regards,

Kabilan K

View solution in original post

15 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

Hi,

PFA,

Regards,

Kabilan K.

Not applicable
Author

Thanks Sander

Leave From and Leave To are empty by ur script hence only "not found" is appearing in "Comment"

Capture2.PNG

Message was edited by: sidrahasan

Not applicable
Author

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.

Not applicable
Author

Hi,

I didn't get u clearly so explain pls explain with sample.

Not applicable
Author

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

Capture.PNG

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.

Not applicable
Author

Please find the sample data attached.

EmployeeIDFromTo
367305-01-201206-01-2012
367313-01-201213-01-2012
367329-03-201229-03-2012
367304-06-201215-06-2012
367313-07-201213-07-2012
367326-07-201226-07-2012

EmployeeIDAbsenceDate
0000367301-01-2012
0000367305-01-2012
0000367306-01-2012
0000367329-03-2012
0000367309-04-2012
0000367321-05-2012
0000367301-06-2012
0000367304-06-2012
0000367305-06-2012
0000367306-06-2012
0000367307-06-2012
0000367308-06-2012
0000367311-06-2012
0000367312-06-2012
0000367313-06-2012
0000367314-06-2012
0000367315-06-2012
0000367318-06-2012
0000367327-06-2012
0000367328-06-2012
0000367329-06-2012
0000367302-07-2012
0000367303-07-2012
0000367306-07-2012
0000367326-07-2012
0000367313-08-2012
0000367316-08-2012
0000367320-08-2012
0000367321-08-2012
0000367322-08-2012
0000367321-09-2012
0000367313-11-2012
0000367314-11-2012
0000367324-12-2012
0000367325-12-2012
0000367326-12-2012
Not applicable
Author

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.