Linking master calendar to multiple tables

[#1]

Hi all,

Could anyone shed any light on why I am unable to connect mulitple tables to a master calendar?

I have 3 tables, each containing a date field, and a single master calendar. Each table is linked by either CallID or AgentID to one another.

Currrently I have only 1 table linked to the master calendar although now require to link the 2 other tables to the calendar, to allow date selections to be made. The link to the master calendar is made using a date field in the table and each of the other tables also contain a date field.

When ever I try to make a connection from the master calendar to one of the other tables QlikView returns "Failed to Load Data" with no error messages??

Please any help would be great

I've attached my table layout.

Cheers, Joe

[Miguel_Angel_Baeyens]

Hello Joe,

Maybe I'm wrong but I think it's as easy as to change (rename in your load script, just in case with AS) all field names in your table of facts to match the name you have in MasterCalendar, i.e.: LOAD ..., AgentTracker.CallDateAT AS CallDate will link AgentTracker with MasterCalendar. Is this what you are looking for?

On the other hand, and likewise, if I'm not wrong, selecting a date in MasterCalendar will select only related fields in all related tables and tables related to related tables... as far as I understand, that's exactly yhe advantage of QlikView ad the Associative Logic.

Regards.

[#3]

Thanks for your help but this doesn't solve the problem,

I beleive that an issue mite be being caused by the fact that if I create a link to the master calendar with one more table. It creates a circular reference?

Cheers,

Joe

[#4]

I'm afraid that if you do that you'll have a circular loop.

What is strange is that you should not have this kind of error message but at the end you would have a loop so this is not good

Either you create different calendars tables or you break the link between Master calendar and all tables and use IF functions / set analysis to compare your data...

Rgds,

Sébastien

Rgds,

[#5]

Thank you, you have confirmed what I thought

I will make mulitple calendars, one for each table.

Thanks, Joe

[#6]

Or try to remove the link between your table and Master Calendar and try if IF statement or Set analysis is working ?

[johnw]

lovellj wrote: I will make mulitple calendars, one for each table.

If all of your date fields are the SAME type of date, you have no reason to add the new date fields. They are redundant data at best.

If your new date fields are DIFFERENT types of dates, such as the difference between an order date, production date, shipment date, and invoice date, then yes, you would normally create one calendar for EACH date, not have a single master calendar. After all, just because you select an order date of August 1, 2009 doesn't mean you want material that was SHIPPED on that date.

A probably less common option is to create a date type table, and allow the user to only select one type of date at a time, allowing one master calendar to take the role of multiple master calendars, but only one at a time. You could then, for example, look at all material ordered on August 1, 2009 by selecting the "ordered date" date type. You could then select the "shipped date" date type to see material shipped on August 1, 2009, without changing your date selection. It's less flexible, as you can't specify matieral ordered on August 1, 2009 and also shipped on August 15, 2009. But sometimes that flexibility isn't needed or even wanted.

[rwunderlich]

I generally approach this by creating a single concatenated date table that uses a different key field for each fact table. For eaxmple, to concatenate the four tables "Qlikview", "Web", "Mainframe" & "Notes" into a single date table using a different timestamp field for each:

SUB loadTime (table, ts)
[Time]:
$(CONCATENATE) LOAD
[$(ts)]
,year([$(ts)]) as "Install Year"
,month([$(ts)]) as "Install Month"
,floor([$(ts)]) as "Install Date"
,hour([$(ts)]) as "Install Hour"
,time([$(ts)]) as "Install Time"
RESIDENT [$(table)]
;
END SUB

LET CONCATENATE='';
CALL loadTime('Qlikview','Qlikview Install Datetime')
LET CONCATENATE='CONCATENATE';
CALL loadTime('Web','Web Install Datetime')
CALL loadTime('Mainframe','Mainframe Install Datetime')
CALL loadTime('Notes','Notes Install Datetime')

-Rob

[Anonymous]

Joe,
The question is not how to do this, but what you're trying to achieve. In you case, what you want to see by selecting, for example, November 13, 2009.
If you can answer this question, you can easy decide if you need single master calandar or multiple calendars.

[#10]

Thank you all,

I have 3 tables, 2 of the tables hold details on Calls and the states they go through (Inialized, connected, on hold, etc) and the other on the Agent activities (Dialing, Active, on call, etc).

Each table has a date field for when the activity starts and the record is written.

My user needs to be able to select a date and see all calls and stats for that date and also see the agent activties on that date?

All the data fields are in the same formatt (DD/MM/YYYY HH:MM:SS)

Cheers, Joe