topic Re: Problem when creating link table from 4 fact tables in QlikView

Problem when creating link table from 4 fact tables

ccaporaso — Mon, 08 Oct 2012 12:46:50 GMT

Hello,

I'm trying to create a QV model based on a complex call center database. I've got my fact tables spread in four:

CD= Has all the calls, no matter if they were successful or not.

AD= Has only calls connected to agents.

QD=Has calls that went through a Queue (not always arrived to an agent)

RD=Has more information about calls that have been queued.

All the tables share four key fields (CD's keys). AD, QD and RD have additional keys.

I built a script and created a Link Table (please, see script attached). It seems to be all fine, but then I realized that I could not get information from QD when there is no AD record (e.g.: I want to analyzed calls that went to a queue but no agent has been reached (abandon calls)).

¿Please, could you tell me if something is wrong with the script? The key fields from each table are the ones that have been used to build the unique key.

Thanks in advance and regards,

Ceci.

Re: Problem when creating link table from 4 fact tables

Mon, 08 Oct 2012 16:05:20 GMT

Hi,

Could you attack a database diagram to understand better the table relationships

Re: Problem when creating link table from 4 fact tables

ccaporaso — Mon, 08 Oct 2012 17:14:28 GMT

Hi Gregorio!

Thanks for your answer!

I'm attaching (as a .PNG file) the complete structure as seen in QV.

Please, keep in mind:

- The script attached in my original post is a brief version: I avoided some unnecessary tables and fields in order to focus on linktable issue.

- The original table names (as you can see in the diagram) is:

CCD=CD

ACD=AD

CQD=QD

QRD=RD

Thanks!

Ceci.

Re: Problem when creating link table from 4 fact tables

Tue, 09 Oct 2012 14:01:10 GMT

Hi ccaporaso,

Why you don't create this key for all tables,

sessionid & sessionseqnum & nodeid & profileid as CommonKey.

I hope helps.

Re: Problem when creating link table from 4 fact tables

ccaporaso — Tue, 09 Oct 2012 18:13:44 GMT

Hi Gregorio!

I've tried creating a common key with the four common fields to build the linktable and that solved the reference problem when calculating abandon calls in queue. However it created additional problems in other indicators.

I think that the basic problem is the cardinality between tables. That can be identified by the qindex field (which is in ACD, CQD, CRD but not in CCD, that means that for each CCD record, there might be 0, 1 or many records in the other three tables).

Is there any guideline to follow when building linktables in complex cases? Most of the examples are quite simple and I'm afraid of being loosing something.

Thanks once more, any idea is welcome.

Ceci.

Re: Problem when creating link table from 4 fact tables

felcar2013 — Thu, 24 Apr 2014 10:06:13 GMT

Hi Cecilia

did you achieve your Goal? I have some indicators loacated in four or five fact tables and wanted to use link tables.

concatenate seems not to be a solution for my case

thanks for any comment

felipe