topic Re: IntervalMatch Issue in QlikView

IntervalMatch Issue

ciaran_mcgowan — Wed, 11 Feb 2015 11:57:02 GMT

Hi all,

I am having an issue using IntervalMatch where I have multiple tables linked using the same field. The MasterCalendar is currently linked to a EventDate but when selecting a date from the MasterCalendar, I need it to return other information relevant to that date.

I have three tables: Department, Employee, Event, all linked using the same field (EmployeeID). What I need to do is not only show me who was involved in the Event (which does), but also the department the Employee was in at the time it was made.

Employee:

LOAD * INLINE [

EmployeeID, EmployeeName

E001, Paul

E002, Ringo

E003, George

E004, John

];

Department:

LOAD * INLINE [

EmployeeID, DepartmentName, StartDate, EndDate,

E001, Guitar, 01-01-1998, 01-01-2005

E002, Drums, 01-01-2008, 01-01-2009

E003, Bass, 01-01-2003, 01-01-2011

E004, Vocals, 01-01-1998, 01-01-2009

E004, Piano, 01-01-2010, 01-01-2015

];

Event:

LOAD * INLINE [

EmployeeID, EventDate

E001, 03-03-2001

E001, 05-05-2003

E004, 02-02-2001

E004, 07-07-2007

E004, 08-08-2014

];

In the above example I would need to know if John was in the "Vocals" or "Piano" department at the time of each event. Using IntervalMatch with the Master Calendar, creates a loop so it's unusable (unless I'm missing something).

Thanks in advance for your help!

Re: IntervalMatch Issue

Anonymous — Wed, 11 Feb 2015 13:07:11 GMT

Hello,

- Create EndDate for Event table as well.

- Flag each table in order to differentiate them later.

- Unite your Department and Event tables via CONCATENATE.

- IntervalMatch with MasterCalendar.

Department:

LOAD * INLINE [

Flag, EmployeeID, DepartmentName, StartDate, EndDate,

1, E001, Guitar, 01-01-1998, 01-01-2005

1, E002, Drums, 01-01-2008, 01-01-2009

1, E003, Bass, 01-01-2003, 01-01-2011

1, E004, Vocals, 01-01-1998, 01-01-2009

1, E004, Piano, 01-01-2010, 01-01-2015

];

concatenate

Event:

LOAD * INLINE [

Flag, EmployeeID, StartDate, EndDate

2, E001, 03-03-2001, 03-03-2001

2, E001, 05-05-2003, 05-05-2003

2, E004, 02-02-2001, 02-02-2001

2, E004, 07-07-2007, 07-07-2007

2, E004, 08-08-2014, 08-08-2014

];

Serhan

Re: IntervalMatch Issue

ciaran_mcgowan — Wed, 11 Feb 2015 15:13:24 GMT

Hi Serhan,

Thanks for the suggestion, I can see that it works in theory (from the example I gave), but in practise the Event and Department have about 20 different columns each.

I don't think it would be practical to concatenate both tables like that as there are so many fields unique to each table.

Re: IntervalMatch Issue

Anonymous — Wed, 11 Feb 2015 15:19:43 GMT

Hello,

It does not matter how many fields they have. Just give the same name to the same (same in the sense that they mean the same thing) fields. This technique is one of the fundamentals of Qlik modelling.

Check out this post.

Concatenate vs Link Table

Serhan

Re: IntervalMatch Issue

ciaran_mcgowan — Wed, 11 Feb 2015 16:23:28 GMT

Hi Serhan,

I've done that now. If I select a date, the correct department now is displayed but the EventID is no longer working. Any date selection means that the EventID selection box becomes greyed out.

Re: IntervalMatch Issue

Anonymous — Wed, 11 Feb 2015 20:28:27 GMT

Hello Ciaran,

There must be something very wrong then since you are completely losing the Event table I guess.

I'll try to create an example if I find any time on Friday. Remind me please if you can't find a solution to your problem already.

Serhan