topic Re: IntervalMatch() - how to use it in this case? in QlikView

IntervalMatch() - how to use it in this case?

datanibbler — Thu, 10 Jul 2014 09:54:12 GMT

Hi,

I have a new requirement, something I haven't done before, at least not in this constellation, so some help would be nice:

- I have a table with all the employees we have (or had) in our company, together with their joining_date and leaving_date
=> There are those that are currently with the company
=> There are those who have already left (with the corresp. leaving_date)

I have no specific date_field in the table. In my app I have a master_calendar, that has every day.

Now I have to use IntervalMatch to find out which employees were there on a given day, so that day (from the calendar)

must be matched to the interval between an employee's joining_date and his/her leaving_date.

I used IntervalMatch() before, but then I had a list - an inline_table - of intervals. This time around, I have two tables - one (the calendar) has every single day and the other has the joining_date and leaving_date.

So how to go about this?

Thanks a lot!

Best regards,

DataNibbler

Re: IntervalMatch() - how to use it in this case?

hic — Thu, 10 Jul 2014 10:05:23 GMT

You should use a While-loop to create all dates in the interval, and then connect the individual dates to your calendar. Take a look at Creating Reference Dates for Intervals.

HIC

Re: IntervalMatch() - how to use it in this case?

datanibbler — Thu, 10 Jul 2014 10:09:37 GMT

Hi Henric,

thanks for the answer!

I don't think I need a new LOOP to create dates, however - I already have that as part of my master_calendar. Most tables in the app, like in all of my apps, are linked via the date, so I necessarily have every date in there.

I'm just trying it out.

Talk to you soon!

Re: IntervalMatch() - how to use it in this case?

hic — Thu, 10 Jul 2014 10:19:09 GMT

I think you do need a loop... The goal is to have three tables:

EmploymentPeriods: One record per employee employment period. I.e. one record per person, unless a person has been employed during two different periods.
EmploymentPeriodDate: One record per date and employment period. I.e. the same date appears several times - once per person.
Master Calendar: One record per date.

You need the loop to create table 2.

HIC

Re: IntervalMatch() - how to use it in this case?

Anonymous — Thu, 10 Jul 2014 10:19:11 GMT

Hi,

You may have to create temp table with following script,

IntervalMatch (master_calendar_date) LOAD joining_date, leaving_date Resident employee;

And join it back to your original employee table.

Re: IntervalMatch() - how to use it in this case?

datanibbler — Thu, 10 Jul 2014 10:24:14 GMT

Hi Henric,

okay, that sounds sensible - and how would I go about creating that table?

Re: IntervalMatch() - how to use it in this case?

hic — Thu, 10 Jul 2014 10:29:10 GMT

Employees:
Load EmployeeD, JoiningDate, LeavingDate, ...
From Employees;

Employees_x_Dates:

Load EmployeeD,
          ...,
          Date( JoiningDate + IterNo() – 1 ) as ReferenceDate
          Resident Employees
          While IterNo() <= LeavingDate - JoiningDate + 1 ;

And then you link your reference date to your calendar. Check the link in my previous answer.

HIC

Re: IntervalMatch() - how to use it in this case?

datanibbler — Thu, 10 Jul 2014 10:37:16 GMT

Ah, ok.

So you do entirely without the IntervalMatch, but instead you "pump up" the table to have one record per day per employee, yes?

OK. That is what I do in some other instances already. I didn't think of it because I was focused on finding a solution using the IntervalMatch() - I guess that is a strong sign that I need to be having lunch 😉 - but I guess that is the easier way.

Thanks a lot!

Best regards,

DataNibbler