IntervalMatch Issue

[ciaran_mcgowan]

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!

[Anonymous]

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

];

BR

Serhan

[ciaran_mcgowan]

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.

[Anonymous]

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

BR

Serhan

[ciaran_mcgowan]

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.

[Anonymous]

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.

BR

Serhan