Date Range Lookup

[#1]

Hi All

I have two sets of data, first is planning data containing fields EmployeeID, Required_From (date field) and Required_To (date field). I then have a transaction table with EmployeeID and Date_Worked. I need a way to create a straight table with a lookup function to check if Date_Worked falls withing Required_From and Required_To for each employee, if true I want to return 1, else 0.

Please see attached spreadsheet for my datasets and required results.

[#2]

Hi,

Can you try below expression. I did not check but it should work.

=IF(num(Date_Worked) >= num(Required_From) and num(Date_Worked) <= num(Required_To)),1,0)

[MindaugasBacius]

Try like this:

Data:
LOAD EmployeeID, 
     Required_From, 
     Required_To
FROM

(ooxml, embedded labels, table is Resource_Plan);


tmp:
LOAD EmployeeID, 
     Date_Worked
FROM

(ooxml, embedded labels, table is Resource_Worked);


Left join (tmp)
IntervalMatch(Date_Worked) LOAD Required_From, Required_To Resident Data;


Right join (tmp)
LOAD *
Resident Data;


Drop Table Data;


Log:
LOAD *
  ,if(IsNull(Date_Worked), 0, 1) as Worked_flag
Resident tmp;


Drop Table tmp;

Result:

See the attached file.

[#4]

Hi Mindaugas

Thanks a lot for your help , how would you approach this if I would like to have a date range from Min() Required_From to Max() Required_To in Date_Worked field?

[MindaugasBacius]

Data:  
LOAD EmployeeID,   
     Min(Required_From) as Required_From,   
     Max(Required_To) as Required_To  
FROM  

(ooxml, embedded labels, table is Resource_Plan)
Group By EmployeeID; 

Check this statement.

[#6]

Hi Sai

Your answer is correct. Thanks a lot ...I don't see the button to mark this as correct though