Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Sort of solved the problem. I've changed the join to create synthetic key. It allows me to produce the desired outcome.
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
A quick solution is having two calendars
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