topic Re: Problem with Loosly Coupled Tables. in QlikView

Problem with Loosly Coupled Tables.

Thu, 05 Nov 2015 10:06:58 GMT

Hi All

I'm having issues trying to join 2 data sets. One from our phone system and the second from our CRM system,

Our phone system logs which line someone called us on and for how long.

Our CRM logs the outcome.

However we don't have a level of integration which gives me a direct link to show which calls resulted in which outcome.

Therefore I’m trying to do is compare these two data sets by client over time so I can monitor peaks in activity as well as if the team are capturing all of the calls received in the CRM system. However if I load these two sets into Qlikview they become loosely coupled.

Below are some example data sets and a desired pivot table outcome.

Attached is a screenshot of the data Diagram

Phone System

Line in	Date	Time on phone
Line A	02/11/2015	14
Line C	02/11/2015	19
Line C	03/11/2015	20
Line C	04/11/2015	21
Line A	05/11/2015	13
Line C	05/11/2015	22
Line B	03/11/2015	16
Line B	04/11/2015	17
Line B	05/11/2015	15
Line B	05/11/2015	18

CRM System

Client	Outcome	Date
Client A	Result A	02/11/2015
Client A	Result B	03/11/2015
Client A	Result A	05/11/2015
Client A	Result A	05/11/2015
Client B	Result B	02/11/2015
Client B	Result B	03/11/2015
Client B	Result B	04/11/2015
Client B	Result B	04/11/2015
Client B	Result B	05/11/2015

Line to client Join

Line in	Client
Line A	Client A
Line B	Client B
Line C	Client A

Desiered Povit table

Date	Cilent	Count CRM_Results Loged	Count of calls
02/11/2015	Client A	1	2
03/11/2015	Client A	1	1
04/11/2015	Client A	0	1
05/11/2015	Client A	2	2
02/11/2015	Client B	1	0
03/11/2015	Client B	1	1
04/11/2015	Client B	2	1
05/11/2015	Client B	1	2

Does anyone have any suggestions

Re: Problem with Loosly Coupled Tables.

Thu, 05 Nov 2015 10:23:46 GMT

Sort of solved the problem. I've changed the join to create synthetic key. It allows me to produce the desired outcome.

Re: Problem with Loosly Coupled Tables.

Anonymous — Thu, 05 Nov 2015 11:10:20 GMT

Hi Shaun,

I would suggest that you keep both tables but create a Fact table to keep your measures. The fact table can hold composite keys that you have created on both dimension tables

Re: Problem with Loosly Coupled Tables.

Clever_Anjos — Thu, 05 Nov 2015 11:24:17 GMT

A quick solution is having two calendars

Re: Problem with Loosly Coupled Tables.

Mark_Little — Thu, 05 Nov 2015 11:55:22 GMT

HI,

Take a look at the attached solution.

Basically, use an apply map to join you clients to the phone data,

Make a link between the two tables of Client + Date.

Then Count Phone Clients and Count CRM clients.

Hope this helps

Mark