topic Re: Date Range Lookup in QlikView

Date Range Lookup

Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Date Range Lookup

Mon, 30 May 2016 06:18:37 GMT

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)

Re: Date Range Lookup

MindaugasBacius — Mon, 30 May 2016 06:31:46 GMT

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.

Re: Date Range Lookup

Mon, 30 May 2016 06:49:57 GMT

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?

Re: Date Range Lookup

MindaugasBacius — Mon, 30 May 2016 07:16:17 GMT

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.

Re: Date Range Lookup

Mon, 30 May 2016 09:30:04 GMT

Hi Sai

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