Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with Loosly Coupled Tables.

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 inDateTime on phone
Line A02/11/201514
Line C02/11/201519
Line C03/11/201520
Line C04/11/201521
Line A05/11/201513
Line C05/11/201522
Line B03/11/201516
Line B04/11/201517
Line B05/11/201515
Line B05/11/201518

CRM System

ClientOutcomeDate
Client AResult A02/11/2015
Client AResult B03/11/2015
Client AResult A05/11/2015
Client AResult A05/11/2015
Client BResult B02/11/2015
Client BResult B03/11/2015
Client BResult B04/11/2015
Client BResult B04/11/2015
Client BResult B05/11/2015

Line to client Join

Line inClient
Line AClient A
Line BClient B
Line CClient A

Desiered Povit table

DateCilentCount CRM_Results LogedCount of calls
02/11/2015Client A12
03/11/2015Client A11
04/11/2015Client A01
05/11/2015Client A22
02/11/2015Client B10
03/11/2015Client B11
04/11/2015Client B21
05/11/2015Client B12

Does anyone have any suggestions

1 Solution

Accepted Solutions
Mark_Little
Luminary
Luminary

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

View solution in original post

4 Replies
Not applicable
Author

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

Anonymous
Not applicable
Author

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

Clever_Anjos
Employee
Employee

A quick solution is having two calendars

Mark_Little
Luminary
Luminary

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