Interview Prep
Career
GenAI
Prompt Engg
ChatGPT
LLM
Langchain
RAG
AI Agents
Machine Learning
Deep Learning
GenAI Tools
LLMOps
Python
NLP
SQL
AIML Projects

Reading list

Machine Learning Basics for a Newbie
6 Steps of Machine learning Lifecycle Introduction to Predictive Modeling
Introduction to Exploratory Data Analysis & Data Insights Descriptive Statistics Inferential Statistics How to Understand Population Distributions?
Reading Data Files into Python Different Variable Datatypes
Probability for Data Science Basic Concepts of Probability Axioms of Probability Conditional Probability
Central Tendencies for Continuous Variables Spread of Data KDE plots for Continuous Variable Overview of Distribution for Continuous variables Normal Distribution Skewed Distribution Skeweness and Kurtosis Distribution for Continuous Variable
Central Tendencies for Categorical Variables Understanding Discrete Distributions Performing EDA on Categorical Variables
Dealing with Missing Values Understanding Outliers Identifying Outliers in Data Outlier Detection in Python Outliers Detection Using IQR, Z-score, LOF and DBSCAN
Sample and Population Central Limit Theorem Confidence Interval and Margin of Error
Bivariate Analysis Introduction
Covariance Pearson Correlation Spearman's Correlation & Kendall's Tau Correlation versus Causation Tabular and Graphical methods for Bivariate Analysis Performing Bivariate Analysis on Continuous-Continuous Variables
Tabular and Graphical methods for Continuous-Categorical Variables Introduction to Hypothesis Testing P-value Two sample Z-test T-test T-test vs Z-test Performing Bivariate Analysis on Continuous-Catagorical variables
Chi-Squares Test Bivariate Analysis on Categorical Categorical Variables
Multivariate Analysis A Comprehensive Guide to Data Exploration The Data Science behind IPL
Supervised Learning vs Unsupervised Learning Reinforcement Learning Generative and Descriminative Models Parametric and Non Parametric model
Machine Learning Pipeline Preparing Dataset Build a Benchmark Model: Regression Build a Benchmark Model: Classification
Evaluation Metrics for Machine Learning Everyone should know Confusion Matrix Accuracy Precision and Recall AUC-ROC Log Loss R2 and Adjusted R2
Dealing with Missing Values Replacing Missing Values Imputing Missing Values in Data Working with Categorical Variables Working with Outliers Preprocessing Data for Model Building
Understanding Cost Function Understanding Gradient Descent Math Behind Gradient Descent Assumptions of Linear Regression Implement Linear Regression from Scratch Train Linear Regression in Python Implementing Linear Regression in R Diagnosing Residual Plots in Linear Regression Models Generalized Linear Models Introduction to Logistic Regression Odds Ratio Implementing Logistic Regression from Scratch Introduction to Scikit-learn in Python Train Logistic Regression in python Multiclass using Logistic Regression How to use Multinomial and Ordinal Logistic Regression in R ? Challenges with Linear Regression Introduction to Regularisation Implementing Regularisation Ridge Regression Lasso Regression
Introduction to K Nearest Neighbours Determining the Right Value of K in KNN Implement KNN from Scratch Implement KNN in Python
Bias Variance Tradeoff Introduction to Overfitting and Underfitting Visualizing Overfitting and Underfitting Selecting the Right Model What is Validation? Hold-Out Validation Understanding K Fold Cross Validation
Introduction to Feature Selection Feature Selection Algorithms Missing Value Ratio Low Variance Filter High Correlation Filter Backward Feature Elimination Forward Feature Selection Implement Feature Selection in Python Implement Feature Selection in R
Introduction to Decision Tree Purity in Decision Tree Terminologies Related to Decision Tree How to Select Best Split Point in Decision Tree? Chi-Squares Information Gain Reduction in Variance Optimizing Performance of Decision Tree Train Decision Tree using Scikit Learn Pruning of Decision Trees
Introduction to Feature Engineering Feature Transformation Feature Scaling Feature Engineering Frequency Encoding Automated Feature Engineering: Feature Tools
Introduction to Naive Bayes Conditional Probability and Bayes Theorem Introduction to Bayesian Adjustment Rating: The Incredible Concept Behind Online Ratings! Working of Naive Bayes Math behind Naive Bayes Types of Naive Bayes Implementation of Naive Bayes
Understanding how to solve Multiclass and Multilabled Classification Problem Evaluation Metrics: Multi Class Classification
Introduction to Ensemble Techniques Basic Ensemble Techniques Implementing Basic Ensemble Techniques Finding Optimal Weights of Ensemble Learner using Neural Network Why Ensemble Models Work well?
Introduction to Stacking Implementing Stacking Variants of Stacking Implementing Variants of Stacking Introduction to Blending Bootstrap Sampling Introduction to Random Sampling Hyper-parameters of Random Forest Implementing Random Forest Out-of-Bag (OOB) Score in the Random Forest IPL Team Win Prediction Project Using Machine Learning Introduction to Boosting Gradient Boosting Algorithm Math behind GBM Implementing GBM in python Regularized Greedy Forests Extreme Gradient Boosting Implementing XGBM in python Tuning Hyperparameters of XGBoost in Python Implement XGBM in R/H2O Adaptive Boosting Implementing Adaptive Boosing LightGBM Implementing LightGBM in Python Catboost Implementing Catboost in Python
Different Hyperparameter Tuning methods Implementing Different Hyperparameter Tuning methods GridsearchCV RandomizedsearchCV Bayesian Optimization for Hyperparameter Tuning Hyperopt
Understanding SVM Algorithm SVM Kernels In-depth Intuition and Practical Implementation SVM Kernel Tricks Kernels and Hyperparameters in SVM Implementing SVM from Scratch in Python and R
Introduction to Principal Component Analysis Steps to Perform Principal Compound Analysis Computation of Covariance Matrix Finding Eigenvectors and Eigenvalues Implementing PCA in python Visualizing PCA A Brief Introduction to Linear Discriminant Analysis Introduction to Factor Analysis
Introduction to Clustering Applications of Clustering Evaluation Metrics for Clustering Understanding K-Means Implementation of K-Means in Python Implementation of K-Means in R Choosing Right Value for K Profiling Market Segments using K-Means Clustering Hierarchical Clustering Implementation of Hierarchial Clustering DBSCAN Defining Similarity between clusters Build Better and Accurate Clusters with Gaussian Mixture Models
Understand Basics of Recommendation Engine with Case Study
8 Ways to Improve Accuracy of Machine Learning Models
Introduction to Dask Working with CuML
Introduction to Machine Learning Interpretability Framework and Interpretable Models model Agnostic Methods for Interpretability Implementing Interpretable Model Understanding SHAP Out-of-Core ML Introduction to Interpretable Machine Learning Models Model Agnostic Methods for Interpretability Game Theory & Shapley Values
Introduction to Machine Learning Interpretability Framework and Interpretable Models model Agnostic Methods for Interpretability Implementing Interpretable Model Understanding SHAP Out-of-Core ML Introduction to Interpretable Machine Learning Models Model Agnostic Methods for Interpretability Game Theory & Shapley Values
Introduction to AutoML Implementation of MLBox Introduction to PyCaret TPOT Auto-Sklearn EvalML
Pickle and Joblib Introduction to Model Deployment
Deploying Machine Learning Model using Streamlit Deploying ML Models in Docker Deploy Using Streamlit Deploy on Heroku Deploy Using Netlify Introduction to Amazon Sagemaker Setting up Amazon SageMaker Using SageMaker Endpoint to Generate Inference Deploy on Microsoft Azure Cloud Introduction to Flask for Model Deploying ML model using Flask
Model Deployment in Android Model Deployment in Iphone

How to remove Synthetic Key using Concatenation & Link table in QlikView?

Sunil Ray
Sunil Ray Last Updated : 26 Jul, 2020
5 min read

In one of my previous articles, we discussed about synthetic keys (Synthetic keys in Qlikview – Simplified). We discussed why synthetic keys are generated and came to conclusion that if we have multiple synthetic keys in our data model, it might be a result of bad bad data model and may deliver unexpected results. We also saw a few ways to remove synthetic keys and improve our data model.

This article starts where we finished our last article. We will discuss two more techniques to remove synthetic keys and optimize our data model in our QlikView application. These two techniques are:-

  1. Concatenation
  2. Link/ Key Table

Let’s understand these two techniques in detail using examples:

Analyse sales trend over years with YoY transaction datasets

A sales oriented company has year on year transaction datasets (one dataset for each year) with one or two different fields (due to base system changes or defects) but rest of the fields are similar. Company wants to show YoY sales trends using these datasets.

In this scenario, let’s load all YoY datasets in QlikView. As you would expect, QlikView creates synthetic keys to join these tables, as these tables have multiple common fields. You can see the data model with synthetic key below. Now to remove synthetic key, we can’t rename/ drop all these fields because they are significant and related with each other. Here, we need all the fields in a table to show YoY trends, monthly seasonality over the year and many more things. As you know, Qlikview automatically concatenate/ combines tables if they have same granularity and columns. However, in our scenario, some of the columns are different. Here we need to force concatenation using CONCATENATE and combine the datastes in a single table (See Snpashot on the right).

Qlik_View_Concatenate

Below, you can also see that in the SALES table, both Employee_Type and Branch_Type are appearing with their available values and total number of records is N1 (number of records in 2010) + N2 (number of records in 2011).

Qlik_View_Concatenate_2

In similar manner, if the granularity and columns in the tables are same, then we can use Concatenate which will merge the tables into one and resulting table will have the sum of rows of the two tables.

Analyze the sales performance of employee against their targets (and analyze performance on several dimensions like product, Year of joining, Region):

To perform this we have five tables, in which two are fact table and others are dimension (Below is table structure).

QlikView_Link_Table

Above you can see that tables, “Sales” and “Plan” have three common fields and Dimension tables are also associated with both the fact tables.

Now, if we load all these tables directly to QlikView, it will result in a data model with synthetic keys (screenshot below).

QlikView_Link_Table_Synthetic_Keys

Since the fact tables do not have similar columns, we can not go for concatenation. At the same time, we need them for our analysis as well. Now to remove synthetic key in this data model we should use LINK table. It links two or more fact tables by taking all common fields out of the original tables and places them into a new table (called link table).The new link table contains all possible combinations of values for the set of fields through a unique key and is associated with the original tables.

In simple words, we can say that the link table replaces the synthetic key table and it has all the combinations of the key fields that are common for fact tables. We should also create a new compound key to connect all three tables (Two Fact tables and Link Table) and remove common fields from fact tables.

Rules to define Link Table:-

  1. Create a key based on common fields of fact tables and break all other association using commenting or renaming.
  2. Make sure that all of the combinations that exist in both fact tables are available in the created link table else it may cause missing out on some records.
  3. The link table must have distinct records.

Now let’s look at the methods to develop data model using Link Table:-

Step – 1 Load facts table, form key for all common fields and comment all common fields.QlikView_Link_Table_Scripts1

Step – 2 Create the Link Table by loading the distinct values of the fact tables

QlikView_Link_Table_Scripts2

Step-3 Load other dimension tables.

QlikView_Link_Table_Scripts3

Step-4 Reload it and we would have following data model without a synthetic key.

QlikView_Link_Table_Model

Above, you can see a data model with Link table and it has all common fields of fact tables.

Link Table Vs Concatenate

In the examples above, we looked at both the scenarios, where we should go with CONCATENATION or LINK table. Both methods have their own advantages, Let’s look at some of these:

  1. If the granularity and fields in the fact tables are same, we should go for Concatenate which will merge the tables into one. When these are different and joined to different dimensions, we will use LINK table.
  2. With Link Tables you can maintain more understandable data model. On the other hand Concatenate is a simplistic approach with excellent performance to handle large volume of data
  3. The choice also depends on what kind of analysis we want to perform and which model will suffice our purpose.

End Note:-

As mentioned before, multiple synthetic keys typically reflects bad data model. We had looked at a few methods to remove synthetic keys in past. In this article, we particularly looked at two methods – LINK table and Concatenation. Both the methods have their own advantages and applications. The choice of method should depend on the business requirement and the kind of analysis required from the data.

Have you found this series useful? We have simplified a complex topic – Synthetic keys and have tried to present it in simple and understandable manner. If you need any more help Synthetic Key and Data model, please feel free to ask your questions through comments below..

If you like what you just read & want to continue your analytics learning, subscribe to our emails, follow us on twitter or like our facebook page.

 

 

Sunil Ray
Sunil Ray

Sunil Ray is Chief Content Officer at Analytics Vidhya, India's largest Analytics community. I am deeply passionate about understanding and explaining concepts from first principles. In my current role, I am responsible for creating top notch content for Analytics Vidhya including its courses, conferences, blogs and Competitions.

I thrive in fast paced environment and love building and scaling products which unleash huge value for customers using data and technology. Over the last 6 years, I have built the content team and created multiple data products at Analytics Vidhya.

Prior to Analytics Vidhya, I have 7+ years of experience working with several insurance companies like Max Life, Max Bupa, Birla Sun Life & Aviva Life Insurance in different data roles.

Industry exposure: Insurance, and EdTech

Major capabilities: Content Development, Product Management, Analytics, Growth Strategy.

Free Courses

Generative AI 4.7

Generative AI - A Way of Life

Explore Generative AI for beginners: create text and images, use top AI tools, learn practical skills, and ethics.
Large Language Models 4.5

Getting Started with Large Language Models

Master Large Language Models (LLMs) with this course, offering clear guidance in NLP and model training made simple.
Prompt Engineering 4.6

Building LLM Applications using Prompt Engineering

This free course guides you on building LLM apps, mastering prompt engineering, and developing chatbots with enterprise data.
LlamaIndex 4.8

Improving Real World RAG Systems: Key Challenges & Practical Solutions

Explore practical solutions, advanced retrieval strategies, and agentic RAG systems to improve context, relevance, and accuracy in AI-driven applications.
RAG systems using LlamaIndex 4.7

Microsoft Excel: Formulas & Functions

Master MS Excel for data analysis with key formulas, functions, and LookUp tools in this comprehensive course.

Recommended Articles

Responses From Readers

Clear

Sub2a
Sub2a

Its really awesome. I have a doubt, Here why you are generating Key and Temp_Key fields, why you are dropping again, Shall we try with single column with the name of Key. Can you please explain.... @Sub2a

123

Satish
Satish

Hi Sunil Thanks for the info. I do have the similar situation 2 fact tables, opportunity and campaign where there are no common fields across these tables. I have opportunity create date in opportunity fact and campaign start date in campaign fact, where i need to derive common date calender. Could you please suggest me on the same. Thanks Satish

123

Kiran Kumar
Kiran Kumar

Hi Sunil, In the second approach(Link Table) we have three columns in Common between the two fact tables i.e. Employee_Id, Product_Code, Branch, what if we try the concatenation concept here.

123

Write for us

Write, captivate, and earn accolades and rewards for your work
  • Reach a Global Audience
  • Get Expert Feedback
  • Build Your Brand & Audience
  • Cash In on Your Knowledge
  • Join a Thriving Community
  • Level Up Your Data Science Game
imag

We use cookies essential for this site to function well. Please click to help us improve its usefulness with additional cookies. Learn about our use of cookies in our Privacy Policy & Cookies Policy.

Show details