Analysis using tables with no common fields

[ttmaroney]

I have two tables, one on help desk tickets (Tickets) and one on telephone calls received by a the help desk (Calls), and these are dispirit systems that are not connected or have a common primary key. Not all tickets are generated from phone calls to the help desk and not all phone calls result in tickets. Further, sometimes people not in the help desk group will close tickets in that queue in error, and I want to exclude these without maintaining a static list of members of the Service Desk group.  Some members of the Service Desk get promoted.

In the Tickets table, I have a Log_Agent (the person who created the ticket), and in the Calls table I have Agent_Name (the person who answered the call).

Calls:

LOAD

     Calls_ID,

     Calls_Date,

     Calls_MonthYear,

     Agent_Name

FROM……

Tickets:

LOAD

     TicketNumber,

     Ticket_Date,

     Ticket_MonthYear,

     Log_Agent

//Log_Anegt as Agent_Name    This I know does NOT work as it links all calls to all tickets

FROM…

What I’m trying to accomplish:

When I filter by Month-Year, I want to see how many calls were answered by each Agent_Name and for only folks on that list, I want to know how many tickets were created by Log_Agent (that same person during the same period), but again only where the Log_Agent name is included in the filtered results of Agent_Name.

I think I need a master calendar that combines the date the ticket was opened with the date the call came in, but all efforts have failed.  I’m hoping someone had already dealt with this- please help.

[Oleg_Troyansky]

Hi Tom,

Overall, it sounds like you need some consulting help, or a Developer class, to teach you the basics of QlikView Data Modeling.

Let me give you some basic pointers though:

1. You definitely shouldn't work with two detached tables - you will end up creating a Cartesian Join between the two, and that is not what you are looking for.

2. Despite the different field names, it looks like the field Date should be the same between the Tickets and Calls - just call it Date.

3. Even though the logging Agent and the answering Agent are not necessarily the same, but your analysis requires comparing Tickets created by the same Agent - so, in essence, you are creating a logical association between the two fields. So, you should probably name them the same - Agent.

4. If you load your two tables with Dates and Agents named the same way, QlikView will create a Synthetic Key. In this particular case, it's relatively harmless and you can keep it (I can't believe that I said it out loud). Preferably, you can build either a Link Table model or a Concatenated Fact model, but you'd need to learn a bit more for that.

5. Finally, create a Master Calendar table that covers the range of the Dates from both tables. You can find numerous script examples of Master Calendars in this forum.

You can learn all of these Data Modeling techniques, including Linked Tables and Concatenated Facts, from my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense. Or, you can learn advanced Data Modelling at the Masters Summit for Qlik.

cheers,

Oleg Troyansky