topic Linking master calendar to multiple tables in QlikView

Linking master calendar to multiple tables

Fri, 13 Nov 2009 20:17:10 GMT

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

Linking master calendar to multiple tables

Miguel_Angel_Baeyens — Fri, 13 Nov 2009 20:35:59 GMT

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.

Linking master calendar to multiple tables

Fri, 13 Nov 2009 21:09:44 GMT

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

Linking master calendar to multiple tables

Fri, 13 Nov 2009 21:35:40 GMT

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,

Linking master calendar to multiple tables

Fri, 13 Nov 2009 22:36:47 GMT

Thank you, you have confirmed what I thought

I will make mulitple calendars, one for each table.

Thanks, Joe

Linking master calendar to multiple tables

Fri, 13 Nov 2009 22:52:50 GMT

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

Linking master calendar to multiple tables

johnw — Sat, 14 Nov 2009 05:37:23 GMT


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.

Linking master calendar to multiple tables

rwunderlich — Sat, 14 Nov 2009 10:01:23 GMT

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

Linking master calendar to multiple tables

Anonymous — Sat, 14 Nov 2009 10:43:20 GMT

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.

Linking master calendar to multiple tables

Mon, 16 Nov 2009 16:41:49 GMT

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

Linking master calendar to multiple tables

Mon, 16 Nov 2009 16:52:17 GMT

This is just my opinion, but I would start with a single table + IF or set analysis, then check if performances are ok.

Linking master calendar to multiple tables

johnw — Tue, 17 Nov 2009 01:39:00 GMT

Sounds to me like you'd just want a single simple Date field. I wouldn't think you'd want, for instance, "Initialized Date", "Connected Date" and so on. You'd just want "Date", and then an "Activity" field or something to hold the values 'Initialized', 'Connected' and so on. Selecting a Date then gives you all activities on that date.

I think this is basically what spastor is suggesting as well. If you actually need a "calls on hold" column in a chart, for instance, you might use an IF or set analysis to get the data, like count({<"Activity"={'On Hold'}>} "Call ID").

I could still be misunderstanding, of course.

Linking master calendar to multiple tables

Tue, 17 Nov 2009 04:07:06 GMT

John explained better than I did. thank you John !

Linking master calendar to multiple tables

Tue, 17 Nov 2009 18:35:39 GMT

Thanks John you are correct, this is what I currently have.

I have a number of calculations to show number of calls on hold etc using data from the inbound table. Although now I need to show the amount of time each agent spends on each activity. This data how ever is stored in a different table (AgentTracking) from the actions themself.

This other table (AgentTracking) also contains a date field which I require to be sorted/selected on, this is where my issue arises as I am unable to join the AgentTracking table to the master calendar as the Inbound table is already connected to the calendar and AgentTracking tables.

When I attempted to create a link between the AgentTracking table and the calendar I get a error msg stating "unable to load data" which I believe is being caused by a circular reference?

How am I able to link both tables to the calendar to allow my user to make date selections on both sets of data?

Thanks,

Joe

Linking master calendar to multiple tables

johnw — Tue, 17 Nov 2009 23:52:57 GMT

So if you select a particular date, you want all actions from the Inbound table that happen on that date, and all actions from the AgentTracking table that occur on that date? I'd probably concatenate both tables into a MasterActions table, and left join in the Agents table. I'd use some sort of action type field to distinguish between all of the different types of actions. There are probably other approaches if you want to keep your tables separate, such as putting ONLY the information causing the loop on the MasterActions table, and otherwise leaving the tables separate. But combining them is an easy way to avoid the circular reference. QlikView's compression should remove the waste caused by some rows using some fields, and other rows using other fields.

Linking master calendar to multiple tables

Mon, 18 Jan 2010 13:55:40 GMT

Dear Rob,

Would you please send a working sample file which has above code. I need to link my multiple date fileds to the master calender. Your code might be useful for me.

Hope to hear you soon.

Best Regards,