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

Synthetic Keys in Qlikview – simplified!

sunil
sunil Last Updated : 26 Jul, 2020
5 min read
Synthetic Key

Synthetic Key

Before I discuss about Synthetic Keys, let’s look at a typical QV data model (in the diagram on right hand). Here, we can see three tables “Sales”, “Branch Mapping” and “$Syn 1″. But, I had loaded only two tables (Sales and Branch Mapping) in QlikView. Another surprise pops up, when I look closely at “Sales” and “Branch Mapping” tables. There is an additional variable “$Syn 1” in both the tables.

When I encountered this effect in the data model for the first time, I was not able to identify why this was happening? To some extent, I was not bothered about this because the output of the dashboard was working fine. But, when my mentor looked at this model, I came to know that “$Syn 1” is a synthetic key and “$Syn 1 Table” is a synthetic table.

What are Synthetic Keys or Tables?

When we load two tables with a common field name in both tables, the tables get associated automatically based on the common fields. This is the associative functionality of QlikView.

However, when we have more than one common field between two or more tables, QlikView creates “SYNTHETIC KEYS” and “SYNTHETIC TABLE”. QlikView adds synthetic table (as $Syn table) and synthetic key (as $Syn symbol) to the data model. The keys are added to the user uploaded tables and are used to join with synthetic table.

Synthetic key is QlikView’s method to deal with composite keys. The Synthetic table contains a synthetic key that is a composite of all the combinations of the multiple key fields connecting the tables. In our data model, we had two fields common “Branch” and “Region” so QlikView created synthetic key and synthetic table on its own. If we look at the source table view of table viewer, it shows that two fields are connected between these two tables.

Synthetic Key

Are Synthetic keys good or bad?

As per QlikView reference manual:

When the number of composite / Synthetic keys increases, depending on data amounts, table structure and other factors, QlikView may or may not handle them gracefully. QlikView may end up using excessive amount of time and/or memory. Unfortunately the actual limitations are virtually impossible to predict, which leaves only trial and error as a practical method to determine them.

  • This is one of the reasons many people remove synthetic keys. But, this statement is applicable when you have multiple synthetic keys in your data model. However, if your data model has multiple synthetic keys, it is likely that there is a serious issue with your data model and not with synthetic keys.
  • When you have two or more tables and they have multiple common fields, then synthetic keys create a correct, compact and efficient solution. Synthetic keys are not the reason for performance and memory problems. They usually work slightly better or similar, when compared to manual composite keys, which are usually used to remove synthetic keys.
  • One of the instances, I remembered that one of my colleague said that due to synthetic key, the output of the data model is not showing correctly or there is performance issue but when he has removed the synthetic keys, it is working fine. Here actual problem was not synthetic keys, it was a poor data model and while removing synthetic keys it has improved. Currently I don’t have performance and memory comparison detail for with or without synthetic keys (if you have please share).

Why should we remove Synthetic keys?

As discussed above, Synthetic keys may get created due to poor data modeling. Whenever an unexpected synthetic key is created, we should look at data model again, make necessary changes and as a result we end up with a good data model. Please note that the important keyword in last statement is UNEXPECTED. Otherwise, synthetic keys don’t do any harm.

Second reason, which I realized over time is whenever I create my own composite keys instead of synthetic keys, I have far higher clarity about the data model.

Othewise, I can say that synthetic keys are good and provide ease to handle composite keys if you are working on a good data model.

How to remove Synthetic keys?

To remove synthetic keys, we first look at our data model and make necessary changes, if required. We have multiple methods to remove synthetic keys but it depends upon the requirement

  • Removing fields: When common fields causing synthetic keys are not required in data model and doing so will not affect the relationship between two tables. Removing fields can be done by commenting or removing field from load script.

Synthetic Key

  • Renaming fields (Using QUALIFY): When common fields causing synthetic keys are not  same field (not containing similar values), These are actually different fields with same name. Renaming can be done by using “AS” clause. We can also achieve this by using QUALIFY statement. With qualified statement, fields names are converted in the “TableName.FieldName” format

Synthetic Key  Using QUALIFYQlik_View_Synthetic_Keys

  •  Autonumber/ Composite Keys: When we know common fields causing synthetic keys are important for data model then we can create our own key to handle composite keys. We can also use Autonumber / Autonumberhash128 / Autonumberhash256 functions to create composite keys. These will create a unique bit value for each distinct combination of the concatenated columns. Autonumberhash128 and Autonumberhash256 creates 128bit and 256bit values respectively. Please note Autonumber may be problematic in applications generating the QVD files for use in other QlikView applications.Synthetic Key
  • We also have other methods to remove synthetic keys like “Creating a link table“, “Creating Explicit Joins ” and “Concatenating similar tables“. These topics must be discussed in detail and will explore it in future.

End Note:

In general, developers want to remove synthetic keys because it is believed that they lead to negative impact on performance and memory utilization. However, this is not true. They handle composite keys in a more efficient way compared to manual keys, if we have a good data model. If you have experienced the difference in performance and memory utilization with or without synthetics, please share your thoughts through the comments below as it will benefit the community.

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.

autonumberautonumberhashdata modelQlikviewQualifySynthetic keysSythetic table
sunil
sunil

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

Abhinav Mishra
Abhinav Mishra

Dear Sunil, I am qlikview enthusiast & looking forward to know the basic concept of qlikview visualization before going ahead in learning this in detail- Can the qlikview dashboard recipient be able to view and leverage the functionality of qlikview if the same(qlikview) is not installed in his machine? Can we host the dashboard over web such that the functionality can be leverage by the end user? Do let me know your comments on this.. thanks,

123

Sub2a
Sub2a

Its help ful, Thanks for sharing

123

alvaro parra
alvaro parra

Thank you dude. Greetings from Bolivia

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
Congratulations, You Did It!
Well Done on Completing Your Learning Journey. Stay curious and keep exploring!
Top 100 Data Science Interview Questions & Answers 2024
Top 100 Data Science Interview Questions & Answers 2024
How to remove Synthetic Key using Concatenation & Link table in QlikView?
How to remove Synthetic Key using Concatenation & Link table in QlikView?

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