Support Portal ContactGet in touch

Qlikview, adding associations in our data

   Words by CRM Consultancy

   on 26/04/2017 10:21:00

Qlikview is all about associations – in finding the common links in the data that relate Entities in the raw data together.

In SQL this might be done as a LEFT or INNER JOIN using a Foreign Key to a Primary Key.

In Dynamics, this could be done via a Link Entity to track a ‘From Entity’ and a ‘To Entity’.

Qlikview is not too different to either, in tracking an association that links 2 sets of data together and builds a common Selection to Join or Pivot the data in our Dashboard.

To get started with, we can identify the Common Field in our Data that will associate one DataSet to another.

This could be (similar to SQL) a Foreign Key to a Parent Key to associate a N:1 Relationship in the data – but could also be any Field with a Common Value; so a good example would be where we have a CRM Global Set spanning multiple Entities, we could define an Association in Qlikview that associates the different Entities together through this Shared Option Set.

The practical purpose being to allow the User to select one or more values for this Global Option Set and then filter all the Data in our Dashboard accordingly – sounds simple, but is deceptively powerful within Qlikview.

To this in practise using the Qlikview Designer, we can review the following steps:

1. Edit Script – within the Script Editor we can review our 2 Datasets; typically these will be padded into 2 different Tabs within the Script Designer.

image

Here we have our Script for how these Datasets are read into the Qlikview App, and how we ‘alias’ the fields.

To build an association, we simply need to alias a Common Field between the two Data Sets:

2. incidentid as %CaseId – in our example, we will look at the Assessment Entity (as a Custom Entity in our Dynamics Solution) and it’s a N:1 Link to the System Case Entity.

In the Assessment Script, we define:

LOAD
    
cw_caseid AS %CaseId
,

This defines that the Script will read back ‘cw_caseid’ as %CaseId via standard SQL Notation.

In the Case Script, we then define the similar:

LOAD
    
incidentid AS %CaseId
,   
    
customerid AS %ClientId
,

This defines a common field in %CaseId that will then become an Association when we Load the data.

 

If we hit RELOAD, we will then see Qlikview build this association whilst loading the data.

 

We can see this Association in Qlikview after the load by dropping down the FILE menu and opening the Table Viewer:

 

image

 

3. The Association in the Dashboard – this association then links the data in the Case Table with the data in the Assessment Table.

 

Superficially this allows us to Filter our Dashboard to select a single Case to then show the particular set of Assessments linked to that Case.

 

We can see this using a List Box to show how selecting a Case (in the below, based on Case Title) then filters the Dashboard to only show Assessments linked to that Case via the %CaseId Association.

 

image

 

4. Putting this Association to Use– so far this is simply a Parent-Child relationship as we might expect it in  any given Reporting Platform.

However we can begin to see the power of Qlikview when we start looking at other Fields we may Track about our Case, such as a Case Stage.

We can add this into the Dashboard as a List Box to then see the Case Stages present for the Cases in our data:

image

The options here for ‘Show Frequency’ immediately giving us a quick insight into the Data, so we can see which Case Stages have the most records of data (either as a Number of a % Ratio) between both the Case Rows and the Assessment Rows.

Share this Article

Search Articles

Filter Articles

CRM Tech DocMan

Recent Articles

CRMCS Quick Start Guide: How To Produce a Microsoft Teams Live Event Dynamics 365 Marketing: Lead Scoring and Sales Acceptance Designing and Developing Microsoft Power Apps Portals Thank You for Attending CRMCS’ Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Thank You for Attending Our Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Webinar: Discover How CRMCS Have United Dynamics 365, SharePoint and Microsoft Teams To Create Sales Excellence Ignite your workflow by adding DocDrive365 to Office 365 The CRMCS guide to everything you need to know about integrating Teams with Dynamics 365 Saving Time By Keeping Documents In One Place TDE Database Encryption with On Premise Dynamics The Key to Successful Compliance in 2020 Part 2: Let’s get GDPR Compliant with Microsoft Power Automate Top 3 Essential Tips for Remote Working Dynamics 365 Marketing: Top 5 Best Features Dynamics Day in the Life - Puma Investments Can you use Teams to amplify collaboration in Dynamics? Part 1: Using a Scheduled Power Automate to Trigger Expiry Date Reminders The secrets of successful document collaboration in Dynamics CRMCS launches new AppSource approved DocDrive365 Dynamics Day in the Life - Moneypenny Release Management Add the App to Dynamics DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Building a New Scheduled Process using Flow
  • "Paul has made a real difference to how my team of 24 people record and store valuable customer data and sales opportunities. Highly recommended."

    James, Operations Director

  • "Understanding your business allows us to advise when to implement aspects of CRM and, likewise, when not to."

    Paul McQuillan, Managing Director

  • "Dynamics 365 and CRMCS have made a real lasting difference to our business, allowing us to replace older systems that were holding back our performance."

    Grahame, Chief Operating Officer

  • "James worked well with us to help connect CRM with Outlook and relate how this might benefit our team using CRM for Property Care."

    Natalie, Property Care Supervisor

  • "Matt was really good with helping us run User Testing on the new Compliance Module of our CRM System."

    Tom, Compliance Administrator

Prefer to go old-school?

Write to us using the below addresses.

Head Office
CRM Consultancy
61 Oxford Street
Manchester
M1 6EQ

London Office
CRM Consultancy London
Grosvenor Avenue
London

Content © CRM Consultancy.