Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

datanibbler
Esteemed Contributor

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


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

Tags (1)
1 Solution

Accepted Solutions

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

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

7 Replies

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

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

datanibbler
Esteemed Contributor

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

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?

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

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

You need the loop to create table 2.

HIC

satyadev_j
Valued Contributor

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

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.

datanibbler
Esteemed Contributor

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

Hi Henric,

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

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

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

datanibbler
Esteemed Contributor

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

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