Mockpreps
🧑💼
🪴
Login now and master your exams with Mockpreps’ mock tests!
Top Interview Questions
QlikView is a leading Business Intelligence (BI) and data visualization tool developed by Qlik, a company that specializes in data analytics software. Launched in the early 2000s, QlikView enables organizations to transform raw data into meaningful insights for decision-making. Unlike traditional reporting tools, QlikView emphasizes an associative data model, allowing users to explore data intuitively and uncover hidden relationships across datasets.
QlikView has gained significant popularity among businesses because it is user-friendly, interactive, and capable of handling complex data analysis scenarios without requiring extensive IT involvement. It is commonly used for reporting, dashboards, data discovery, and predictive analysis.
Associative Data Model
One of the most unique features of QlikView is its associative data model. Unlike SQL-based tools that rely on predefined queries and joins, QlikView allows users to explore data freely. The associative model automatically identifies relationships across multiple datasets, helping users uncover insights without needing to write complex queries.
In-Memory Processing
QlikView stores and processes data in memory rather than relying on disk-based queries. This in-memory technology enables extremely fast data analysis and real-time exploration, even with large volumes of data.
Data Visualization
QlikView offers a wide range of interactive charts, graphs, tables, and dashboards. Users can create visually compelling reports with drag-and-drop functionality. The interactive nature of visualizations allows users to click on any data point and instantly see related information.
Self-Service BI
With QlikView, business users can create their own reports and dashboards without depending on IT teams. The self-service BI approach reduces the turnaround time for insights and fosters data-driven decision-making across organizations.
Advanced Analytics
QlikView supports advanced analytics, including trend analysis, forecasting, and predictive modeling. Users can perform complex calculations, aggregation, and data transformations using QlikView’s scripting language and functions.
Data Integration
QlikView can integrate data from multiple sources, including relational databases, spreadsheets, web services, ERP systems, and cloud platforms. Its ETL (Extract, Transform, Load) capabilities allow data to be cleaned, transformed, and loaded efficiently for analysis.
Collaboration and Sharing
QlikView allows users to share dashboards and reports across teams or departments. It supports role-based access control to ensure that sensitive information is only visible to authorized users.
QlikView’s architecture is designed to provide fast, flexible, and scalable analytics. It consists of three main layers:
Data Layer
The data layer is responsible for collecting and integrating data from various sources. Using QlikView’s ETL tools, users can extract data from multiple databases, transform it into the desired format, and load it into the QlikView application. This layer ensures that data is consistent, accurate, and ready for analysis.
In-Memory Engine
At the heart of QlikView is its in-memory engine. This engine compresses data and stores it in RAM, enabling lightning-fast calculations and queries. The in-memory approach eliminates the need for multiple database queries, significantly reducing response time and enhancing performance.
Presentation Layer
The presentation layer is where users interact with data. QlikView provides an intuitive interface with dashboards, charts, tables, and other visualization components. Users can perform data analysis, apply filters, drill down into details, and gain insights in real-time.
QlikView uses a proprietary scripting language to load and transform data. The script is written in a QlikView load script editor and provides functionality for:
Connecting to different data sources.
Performing data transformations such as joins, concatenations, and aggregations.
Cleaning and validating data before it is loaded into memory.
Creating calculated fields and applying business logic.
A typical QlikView data load script might include commands to connect to a SQL database, extract customer and sales data, and transform it for visualization. The scripting capability is highly flexible, allowing developers to handle complex ETL processes within QlikView itself.
Dashboards in QlikView are highly interactive and allow users to explore data dynamically. Key features of QlikView dashboards include:
Associative Filtering
Users can filter data across multiple dimensions simultaneously. Selecting one data point automatically updates all related visualizations, revealing hidden patterns and correlations.
Drill-Down Analysis
QlikView dashboards support hierarchical data exploration. Users can drill down from high-level summaries to granular details, making it easier to investigate anomalies and trends.
Customizable Visualizations
QlikView offers multiple chart types, including bar charts, line charts, pie charts, scatter plots, gauges, and heat maps. Users can customize the appearance and behavior of visualizations according to their requirements.
Real-Time Analysis
Because of the in-memory engine, dashboards respond instantly to user interactions. This allows real-time monitoring of key performance indicators (KPIs) and faster decision-making.
Speed and Performance
QlikView’s in-memory architecture ensures rapid data analysis and instant response times, even with large datasets.
Intuitive User Interface
The user-friendly interface makes it easy for non-technical users to navigate, filter, and analyze data.
Data Discovery
QlikView enables users to discover insights by exploring relationships between data points, rather than relying solely on predefined queries.
Cost-Effective BI Solution
With its self-service capabilities, QlikView reduces dependence on IT teams, saving both time and costs associated with report generation.
Scalability
QlikView can handle large volumes of data and can scale to meet the growing demands of organizations.
Data Security
QlikView provides robust security features, including role-based access control, data encryption, and user authentication to protect sensitive business information.
Qlik, the company behind QlikView, also offers Qlik Sense, another BI tool. While QlikView focuses on guided analytics and developer-driven dashboards, Qlik Sense emphasizes self-service and modern visualization. Key differences include:
QlikView: Primarily designed for guided analytics and enterprise reporting; developers create dashboards for end-users.
Qlik Sense: Focuses on self-service data discovery and drag-and-drop visualizations; users can build their own dashboards more easily.
User Experience: Qlik Sense has a modern UI and responsive design for mobile devices, while QlikView has a more traditional interface.
Deployment: Both tools support on-premises and cloud deployment, but Qlik Sense has stronger cloud-native capabilities.
Sales and Marketing Analysis
Organizations use QlikView to track sales performance, analyze customer behavior, and evaluate marketing campaigns. Interactive dashboards help identify top-selling products, revenue trends, and customer segmentation.
Financial Reporting
QlikView enables finance teams to generate real-time financial statements, monitor budgets, and track expenses. Drill-down capabilities allow detailed analysis of financial transactions.
Supply Chain Management
QlikView helps supply chain managers optimize inventory, monitor supplier performance, and forecast demand. It provides visibility across procurement, production, and logistics processes.
Human Resources Analytics
HR teams use QlikView to analyze employee performance, retention rates, and workforce demographics. Data-driven insights assist in recruitment planning and talent management.
Healthcare Analytics
Hospitals and healthcare providers leverage QlikView to monitor patient outcomes, track resource utilization, and improve operational efficiency.
While QlikView offers numerous advantages, it also has some limitations:
Steep Learning Curve: Developing complex dashboards and writing scripts require specialized skills.
Licensing Cost: QlikView can be expensive for small businesses due to its licensing model.
Less Modern Interface: Compared to Qlik Sense, QlikView’s interface can feel outdated.
Limited Mobile Support: Although QlikView supports mobile devices, its user experience is less optimized than Qlik Sense.
Answer:
QlikView is a Business Intelligence (BI) and data visualization tool developed by QlikTech. It helps users analyze data, create dashboards, and generate insights. QlikView allows interactive analysis, enabling decision-makers to discover trends, patterns, and correlations in large datasets.
Answer:
In-memory processing: Loads data into memory for fast analysis.
Associative model: Automatically links data across different tables.
Interactive dashboards: Users can filter, drill-down, and explore data.
Data visualization: Supports charts, graphs, tables, and reports.
Script-based ETL: Extract, Transform, Load operations can be scripted.
Collaboration: Reports and dashboards can be shared across teams.
Answer:
| Feature | QlikView | Qlik Sense |
|---|---|---|
| User Interface | Pre-defined dashboards | Self-service visualization |
| Learning Curve | Steeper | Easier for business users |
| Development | Developer-driven | User-driven |
| Visualization | Limited flexibility | Highly interactive |
| Mobile Support | Basic | Responsive, mobile-ready |
Answer:
QlikView architecture has three main components:
QlikView Server (QVS): Manages data and user requests.
QlikView Publisher (QVP): Automates data reloads and distribution.
QlikView Client (Desktop/AccessPoint): User interface to create dashboards or view reports.
Data Flow:
Data → QlikView Script → QlikView Memory → QlikView Server → Dashboards/Reports
Answer:
QlikView uses an associative data model where all tables are linked using keys. The model allows fast associative search, meaning when you select a value in one table, it filters related values in all other linked tables automatically.
Star Schema: Recommended for better performance.
Snowflake Schema: Can be used but may reduce performance.
Answer:
Charts: Bar, Line, Pie, Combo, Gauge, Scatter.
List Boxes: Display values from a field.
Tables: Straight or Pivot tables for detailed analysis.
Text Objects: Show static or dynamic text.
Buttons: For navigation and triggers.
Filters/Selectors: For interactive data filtering.
Answer:
QlikView Script is used for data extraction, transformation, and loading (ETL).
It is written in the Edit Script editor in QlikView Desktop.
Supports loading data from multiple sources like SQL, Excel, CSV, or Web.
Example:
LOAD CustomerID, CustomerName, Country
FROM Customers.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Answer:
QVD stands for QlikView Data file.
It stores QlikView tables in optimized format for fast loading.
Mainly used for data sharing between QlikView applications.
Loading data from QVD is faster than loading from a database.
Answer:
QlikView supports:
Inner Join: Returns only matching records from both tables.
Left Join: Returns all records from the left table and matching records from the right table.
Right Join: Returns all records from the right table and matching from the left.
Outer Join: Returns all records from both tables.
Example:
LEFT JOIN (Table1)
LOAD CustomerID, Sales
FROM Sales.csv;
Answer:
Occurs when two or more tables have multiple common fields.
QlikView automatically creates a synthetic table to link them.
Can cause performance issues and should be avoided using Rename Fields or Concatenate.
Answer:
Happens when tables are linked in a loop.
Leads to incorrect data association.
Avoid by breaking the loop using Rename, Concatenate, or ApplyMap.
Answer:
Used to analyze data based on specific conditions without changing current selections.
Allows comparisons like YTD, Previous Year, or specific filters.
Example:
Sum({<Year={2024}>} Sales)
Calculates total sales only for 2024, ignoring current selections.
Answer:
Displays all distinct values of a field.
Helps in filtering data.
Commonly used for interactive dashboards.
Answer:
Triggers automate actions when certain events occur.
Types include:
OnOpen: Executes when the document opens.
OnActivateSheet: Executes when a sheet is activated.
OnSelect: Executes when a user selects a value.
Answer:
Use QVD files for pre-processed data.
Minimize synthetic keys and circular references.
Use optimized scripts and reduce calculations on charts.
Avoid loading unnecessary fields.
Use star schema for data modeling.
Answer:
Allows creating multiple selections for the same field in a document.
Useful for comparisons between different scenarios.
Example: Comparing sales of two regions in one dashboard.
Answer:
QlikView can load data from:
Databases: SQL Server, Oracle, MySQL
Files: Excel, CSV, TXT, XML
Web sources: REST API or XML/JSON feeds
Data is then joined and transformed using QlikView Script.
| Feature | Pivot Table | Straight Table |
|---|---|---|
| Layout | Can pivot rows & columns dynamically | Static columns and rows |
| Use Case | Summarized, multi-dimensional view | Detailed, tabular view |
| Interactivity | High | Low |
Answer:
Loads only new or changed data instead of the entire dataset.
Improves performance and reduces reload time.
Achieved using QVD files and timestamps.
Sum(FieldName) – Total of numeric field.
Count(FieldName) – Count of values.
Avg(FieldName) – Average value.
Max(FieldName) / Min(FieldName) – Max/Min values.
If(Condition, TrueValue, FalseValue) – Conditional expression.
Creates interactive dashboards for managers.
Provides real-time insights and reporting.
Helps in trend analysis, forecasting, and decision making.
Integrates data from multiple sources into one consolidated view.
Answer:
Saves the current selection state of a document.
Users can reuse or share bookmarks to view data with the same filters.
23. What are QlikView Actions?
Answer:
Actions in QlikView are operations that can be triggered by objects like buttons or list boxes. They automate tasks for better interactivity.
Examples include:
Selecting values
Opening a URL or document
Activating a sheet
Applying bookmarks
Exporting data
Answer:
QVX (QlikView Data eXchange) is a data file format used to exchange data between QlikView and other applications.
It is optimized for speed and can be loaded faster than databases.
Often used when extracting large datasets from QlikView applications for other uses.
Answer:
Use IsNull() function to check if a field is null.
Use Alt(Field, 0) to replace null with a default value.
Example:
LOAD CustomerID, Alt(Sales, 0) as Sales
FROM Sales.csv;
Null handling is essential for accurate calculations and reports.
Answer:
| Feature | Resident Load | Inline Load |
|---|---|---|
| Source | Existing QlikView table in memory | Values defined within script |
| Use Case | Transform data already loaded | Quick data entry or small lookup tables |
| Example | LOAD CustomerID, Sales FROM SalesTable RESIDENT TempTable; |
LOAD * INLINE [ID, Name 1, John 2, Mary]; |
Answer:
Calendar table is used for time-based analysis like YTD, MTD, QTD.
Example Script:
TempCalendar:
LOAD
Date(MakeDate(2024,1,1)+IterNo()-1) as CalendarDate
AUTOGENERATE 365;
MasterCalendar:
LOAD
CalendarDate,
Year(CalendarDate) as Year,
Month(CalendarDate) as Month,
Day(CalendarDate) as Day,
Week(CalendarDate) as Week
RESIDENT TempCalendar;
Helps in joining date fields across multiple fact tables.
Answer:
| Feature | ApplyMap() | Join |
|---|---|---|
| Usage | Lookup a value from a mapping table | Combine two tables based on a common field |
| Performance | Faster for large datasets | Slower if tables are large |
| Example | ApplyMap('MapRegion', CustomerID, 'Unknown') |
LEFT JOIN (Sales) LOAD CustomerID, Region FROM Customers; |
Answer:
Concatenate: Combines two tables vertically (adds rows).
Join: Combines two tables horizontally based on common fields.
Example:
CONCATENATE (Sales)
LOAD * FROM Sales_Q1.csv;
Answer:
A small table used with ApplyMap() to replace or map values.
Improves performance compared to joins.
Example:
MapRegion:
MAPPING LOAD CustomerID, Region FROM Customers.csv;
LOAD CustomerID, ApplyMap('MapRegion', CustomerID) as Region
FROM Sales.csv;
Answer:
Load only necessary fields.
Use resident load and mapping tables instead of multiple joins.
Use QVD files for incremental load.
Avoid synthetic keys and circular references.
Use optimized functions (Alt(), ApplyMap()) instead of complex joins.
Pre-aggregate data in the database if possible.
Answer:
Flags are calculated fields used for conditional analysis.
Example:
LOAD *,
IF(Sales > 1000, 1, 0) as HighSalesFlag
FROM Sales.csv;
Helps in filtering or creating KPIs.
Answer:
| Feature | Set Analysis | IF Statement |
|---|---|---|
| Purpose | Filters data independently of current selection | Conditional calculation depends on selection |
| Performance | Faster for large datasets | Slower if used inside charts |
| Example | Sum({<Year={2024}>} Sales) |
Sum(IF(Year=2024, Sales)) |
Answer:
Use QVD files for faster loads.
Pre-aggregate data where possible.
Load data incrementally using timestamp or ID.
Avoid complex expressions in charts.
Use star schema for relational data modeling.
Answer:
Normal Expression: Basic field calculation like Sum(Sales).
Aggregate Expression: Summarized by dimensions like Sum(Sales)/Count(CustomerID).
Conditional Expression: Depends on a condition, e.g., Sum(IF(Region='North', Sales)).
Answer:
KPI (Key Performance Indicator) shows important metrics at a glance.
Created using Text Objects or Charts with expressions.
Example:
Text Object: ='Total Sales: ' & Sum(Sales)
Can be enhanced using colors and conditional formatting.
Answer:
Drill-down allows users to expand and collapse dimensions in charts.
Steps:
Go to Edit Dimension in chart properties.
Select Create Drill-Down Dimension.
Add multiple levels (e.g., Country → State → City).
Used in hierarchical analysis.
Answer:
Load only new or modified records.
Example script:
Sales:
LOAD * FROM Sales.csv
WHERE Date > (SELECT Max(Date) FROM Sales_QVD);
STORE Sales INTO Sales.QVD;
Reduces reload time and system load.
Answer:
Section Access: Controls access at document and field level.
Steps:
Define a table with USERID, PASSWORD, ACCESS (ADMIN/USER).
Load table in Section Access script.
Example:
SECTION ACCESS;
LOAD * INLINE [
USERID, PASSWORD, ACCESS
ADMIN, admin123, ADMIN
JOHN, john123, USER
];
Ensures row-level security.
Answer:
| Trigger | Execution |
|---|---|
| OnOpen | Executes when QlikView document opens |
| OnActivate Sheet | Executes when a specific sheet is opened |
Useful for auto-loading data or applying default selections.
Answer:
Identify common keys to link tables.
Avoid synthetic keys by renaming fields.
Use Concatenate if tables have similar structure.
Use ApplyMap for mapping small reference tables.
| Feature | QlikView Desktop | QlikView Server |
|---|---|---|
| Purpose | Development & testing | Production & distribution |
| Users | Single developer | Multiple end-users |
| Document Access | Local PC | Web via AccessPoint |
| Data Reload | Manual | Scheduled using Publisher |
Answer:
Allows multiple independent selections in the same dashboard.
Example: Compare Sales of Region A vs Region B using two list boxes with different states.
Achieved by assigning Alternate State in properties of objects.
Answer:
Variables store values or expressions.
Syntax:
SET vSalesTarget = 100000;
LET vToday = Today();
Can be used in expressions, charts, or scripts.
Answer:
Saves the current selection state.
Users can return to the saved state anytime.
Useful for quick navigation and sharing insights.
Answer:
Identify loops in table links.
Use Rename, Concatenate, or Mapping Tables to break loops.
Ensure a star schema design.
Answer:
Maintain a QVD of already loaded data.
Load only new records based on a timestamp.
Concatenate with QVD and store updated QVD.
Example:
OldSales:
LOAD * FROM Sales.qvd (qvd);
NewSales:
LOAD * FROM Sales.csv
WHERE Date > max(OldSales.Date);
Concatenate(OldSales)
LOAD * RESIDENT NewSales;
STORE OldSales INTO Sales.qvd (qvd);
Answer:
Identify slow charts or calculations.
Use QlikView Performance Monitor.
Optimize data model and scripts.
Reduce synthetic keys, circular references, and complex expressions.
Minimize chart objects per sheet.
Answer:
Use expressions in chart Caption or Text Objects.
Example:
='Sales Report for ' & GetFieldSelections(Region)
Changes dynamically with user selection.
Answer:
Use Text Object or Gauge.
Apply color expressions based on thresholds.
Example:
Background Color = IF(Sum(Sales) > 100000, Green(), IF(Sum(Sales) > 50000, Yellow(), Red()))
Answer:
QlikView uses an associative in-memory data model, allowing data from multiple sources to be linked automatically through common fields (keys).
Selection Propagation: Selecting a value in one table filters all related tables dynamically.
Advantages:
Quick data exploration without predefined queries.
Easy handling of multiple fact tables with dimensions.
Supports dynamic filtering in dashboards.
Answer:
Data Model Optimization:
Use star schema instead of snowflake.
Avoid synthetic keys and circular references.
Use numeric keys instead of text keys for joins.
Script Optimization:
Load only required fields.
Use ApplyMap instead of Joins where possible.
Load data incrementally with QVDs.
Dashboard Optimization:
Minimize the number of charts per sheet.
Avoid complex nested expressions.
Use calculated dimensions carefully.
Answer:
Purpose: Load only new or changed records to improve reload time.
Steps:
Maintain a QVD of previously loaded data.
Load new data based on a timestamp or ID.
Concatenate new data with QVD.
Store updated data back into QVD.
Example:
OldSales:
LOAD * FROM Sales.qvd (qvd);
NewSales:
LOAD * FROM Sales.csv
WHERE Date > Max(OldSales.Date);
Concatenate (OldSales)
LOAD * RESIDENT NewSales;
STORE OldSales INTO Sales.qvd (qvd);
Answer:
Section Access is used for row-level security in QlikView.
Controls who can view data and at what level.
Example:
SECTION ACCESS;
LOAD * INLINE [
USERID, PASSWORD, ACCESS, REGION
ADMIN, admin123, ADMIN, *
JOHN, john123, USER, North
MARY, mary123, USER, South
];
SECTION APPLICATION;
LOAD * FROM Sales.csv;
Users see only data permitted by their REGION.
Answer:
| Feature | QVD | QVX |
|---|---|---|
| Full Form | QlikView Data | QlikView Data eXchange |
| Usage | Store tables for QlikView reload | Exchange data between apps |
| Format | Optimized for speed | Can be read by QlikView and other apps |
| Performance | Faster | Slower than QVD |
Answer:
Synthetic Key: Automatically created when two or more tables share multiple common fields.
Can cause performance issues and incorrect associations.
Ways to avoid:
Rename fields to avoid multiple key matches.
Concatenate tables if structures are similar.
Use Mapping Tables and ApplyMap() function.
Answer:
Circular Reference: When tables are linked in a loop, QlikView cannot resolve associations.
Causes incorrect data or synthetic keys.
Solutions:
Break loops by renaming fields.
Use Concatenate instead of joining.
Create a link table.
Answer:
Use QVDs for pre-processed data.
Load only required fields.
Aggregate data in the source database if possible.
Use Incremental Load for efficiency.
Minimize calculated dimensions in charts.
Answer:
Purpose: To enable time-based analysis (YTD, MTD, QTD).
Example Script:
TempCalendar:
LOAD Date(MakeDate(2024,1,1) + IterNo() - 1) as CalendarDate
AUTOGENERATE 365;
MasterCalendar:
LOAD
CalendarDate,
Year(CalendarDate) as Year,
Month(CalendarDate) as Month,
Day(CalendarDate) as Day,
Week(CalendarDate) as Week
RESIDENT TempCalendar;
Joins fact tables with calendar table using CalendarDate.
Answer:
Set Analysis is used for analyzing data independent of current selections.
Commonly used for comparisons like YTD, PY, or conditional metrics.
Example:
Sum({<Year={2024}>} Sales)
Calculates total sales for 2024, ignoring current selections.
Answer:
| Feature | ApplyMap() | Join |
|---|---|---|
| Usage | Lookup values from a mapping table | Combine tables horizontally |
| Performance | Faster for large datasets | Slower for large tables |
| Example | ApplyMap('MapRegion', CustomerID, 'Unknown') |
LEFT JOIN (Sales) LOAD CustomerID, Region FROM Customers; |
Answer:
Alternate State: Allows multiple independent selections in the same dashboard.
Scenario: Compare sales of Region A vs Region B using two list boxes with different states.
Answer:
Use Section Access with field mapping.
Dynamically assign user permissions based on a user table in the database.
Example:
SECTION ACCESS;
LOAD USERID, ACCESS, REGION
FROM Users.csv;
Ensures users see only allowed data.
Answer:
Triggers automate actions based on events.
Types:
OnOpen: Runs when document opens.
OnActivateSheet: Runs when a sheet is activated.
OnSelect: Runs when a field value is selected.
Use Case: Auto-filtering, reloading variables, or refreshing charts.
Answer:
Identify slow charts using Performance Monitor.
Check synthetic keys and circular references.
Use optimized scripts, incremental load, and QVDs.
Reduce number of objects per sheet.
Avoid complex nested expressions in charts.
Answer:
Drill-down dimensions allow users to expand hierarchical data in charts.
Example: Country → State → City.
Steps:
Create a drill-down dimension in chart properties.
Add levels in sequence.
Users can click to expand/collapse levels dynamically.
Answer:
KPIs highlight important metrics like Sales, Profit, etc.
Traffic Lights: Color-coded indicators for thresholds.
Example:
Background Color = IF(Sum(Sales) > 100000, Green(), IF(Sum(Sales) > 50000, Yellow(), Red()))
Can be added to Text Objects or Gauges.
Answer:
Use Link Tables for common dimensions.
Avoid synthetic keys by renaming key fields.
Use concatenate for similar tables.
Ensure star schema design for faster performance.
Answer:
Variables store values or expressions.
Can be used in charts, scripts, or dynamic text objects.
Example:
SET vSalesTarget = 100000;
LET vToday = Today();
Variables enable dynamic calculations and reusability.
Answer:
Real-time data can be handled via:
Direct query from database (ODBC/ODBC Connectors).
Scheduled incremental reloads with QVDs.
Push data from other applications using APIs.
Important for dashboards requiring near real-time updates.
Answer:
Use IF conditions or dollar-sign expansion in chart dimensions.
Example:
=IF(GetSelectedCount(Region)=1, Country, Region)
Changes dimension based on user selection.
Answer:
Load only necessary fields.
Use ApplyMap instead of multiple joins.
Store intermediate results in QVDs.
Avoid synthetic keys and circular references.
Use incremental loads to reduce reload time.
Comment scripts for documentation and maintainability.
| Feature | Star Schema | Snowflake Schema |
|---|---|---|
| Structure | Central fact table + dimension tables | Fact table + normalized dimensions |
| Performance | Faster | Slower due to joins |
| Usage | Recommended for QlikView | Avoid if possible |
Answer:
Create a Link Table combining all common keys.
Rename fields in tables to avoid multiple matches.
Use ApplyMap for small reference tables instead of joins.
Answer:
Aggr() – Nested aggregation for multi-dimensional calculations.
Rank() – Rank values dynamically.
RangeSum() – Sum over a range.
FirstSortedValue() – Fetch first value based on sorting criteria.
Above()/Below() – For trend or moving calculations.
Answer:
Aggr() is used for nested aggregations in QlikView.
It creates a temporary virtual table to perform calculations at a dimension level.
Example:
Sum(Aggr(Sum(Sales), Region, Product))
This calculates the sum of sales for each Region and Product, then totals them.
Commonly used in rankings, percentages, and multi-dimensional calculations.
Answer:
Returns the first value based on sorting order in a dimension.
Example:
FirstSortedValue(Product, -Sales)
Returns the product with the highest sales.
Useful for top N analysis or leaderboard dashboards.
Answer:
Above(): Returns the value of a field above the current row in a chart.
Below(): Returns the value below the current row.
Example:
Sum(Sales) - Above(Sum(Sales))
Calculates difference from previous row; useful for trend and growth analysis.
Answer:
Rank() assigns rank based on a measure in a chart.
Example:
Rank(Sum(Sales))
Can be used to highlight top-selling products or regions.
Supports ascending and descending ranking.
Answer:
| Feature | Aggregate Expression | Calculated Dimension |
|---|---|---|
| Purpose | Calculates measures like Sum, Count | Defines dimensions dynamically |
| Example | Sum(Sales) |
=IF(Region='North','North Region','Other') |
| Use Case | Metrics & KPIs | Dynamic grouping & drill-down |
Answer:
Rolling aggregations calculate running totals or moving averages.
Use Above() and RangeSum() functions.
Example for 3-month rolling sales:
RangeSum(Above(Sum(Sales), 0, 3))
Adds current and previous 2 months’ sales.
Answer:
A link table resolves synthetic keys in multiple fact tables scenario.
Steps:
Identify common fields across fact tables.
Create a link table with these fields.
Connect all fact tables via the link table.
Ensures correct associations without synthetic keys.
Answer:
| Feature | Resident Load | Mapping Load |
|---|---|---|
| Source | Existing QlikView table in memory | Mapping table for ApplyMap() |
| Purpose | Transform or filter data | Lookup values efficiently |
| Performance | Moderate | Very fast for small tables |
Answer:
Purpose: Load only new or updated data to improve performance.
Steps:
Maintain a QVD of previously loaded data.
Identify new rows using timestamp or autonumber ID.
Concatenate new rows with QVD.
Store updated QVD.
Example:
OldData:
LOAD * FROM Sales.qvd (qvd);
NewData:
LOAD * FROM Sales.csv
WHERE OrderDate > Num(Max(OldData.OrderDate));
Concatenate(OldData)
LOAD * RESIDENT NewData;
STORE OldData INTO Sales.qvd (qvd);
Answer:
Dynamic Section Access allows user-level security from a database or file table instead of static inline load.
Example:
SECTION ACCESS;
LOAD USERID, ACCESS, REGION
FROM Users.csv;
Dynamically restricts rows based on user login.
Essential for enterprise-grade security.
Answer:
Alternate states allow multiple independent selections in a dashboard.
Steps:
Create Alternate State in Document Properties.
Assign charts/list boxes to a specific state.
Scenario: Compare sales performance of two regions simultaneously.
Example:
Sum({[StateA]<Year={2024}>} Sales)
Answer:
ApplyMap() is faster than JOIN for small reference tables.
Used to map values without adding extra rows.
Example:
LOAD CustomerID, ApplyMap('MapRegion', CustomerID, 'Unknown') as Region
FROM Sales.csv;
Improves reload performance and reduces synthetic key creation.
Answer:
Use a Link Table to connect shared dimensions.
Rename conflicting fields in fact tables.
Use ApplyMap for small reference tables instead of joins.
Goal: Maintain associations without creating synthetic tables.
Answer:
Reduce number of objects per sheet.
Avoid nested expressions in charts.
Use calculated dimensions carefully.
Pre-load aggregated data using QVDs.
Minimize real-time calculations on large datasets.
Use conditional show/hide for charts.
Answer:
Set Analysis allows dynamic filtering independent of current selections.
Examples:
Compare current year vs previous year sales:
Sum({<Year={2024}>} Sales) - Sum({<Year={2023}>} Sales)
Filter by specific products or regions:
Sum({<Region={'North'}, Product={'Laptop'}>} Sales)
Helps in dashboards requiring comparative KPIs.
Answer:
Use IF statements or dollar-sign expansion for dynamic grouping.
Example:
=IF(GetSelectedCount(Region)=1, Country, Region)
Adjusts dimension based on user selection, useful in interactive dashboards.
Answer:
Methods:
Direct query from database using ODBC/OLEDB.
Scheduled reloads with incremental updates.
Push data via API into QVDs for dashboard refresh.
Ensures dashboards show near real-time data for decision making.
Answer:
Mistakes:
Loading unnecessary fields.
Multiple joins causing synthetic keys.
Circular references.
Nested IFs in charts causing performance lag.
Avoidance:
Use star schema.
Use ApplyMap instead of joins.
Pre-aggregate large datasets.
Comment scripts for clarity.
Answer:
KPIs highlight important metrics with color coding.
Example for sales thresholds:
Background Color = IF(Sum(Sales)>100000, Green(),
IF(Sum(Sales)>50000, Yellow(), Red()))
Can be applied in Text Objects, Gauges, or Charts.
Answer:
Use a currency mapping table with exchange rates.
Apply ApplyMap or JOIN to calculate values in a single currency.
Example:
LOAD Amount*ApplyMap('CurrencyRate', Currency,'1') as AmountInUSD
FROM Sales.csv;
Ensures consistent financial reporting.
Answer:
Modify Master Calendar script to include Fiscal Year and Fiscal Quarter.
Example:
LOAD *,
Year(CalendarDate) as Year,
Month(CalendarDate) as Month,
IF(Month(CalendarDate)>=4, Year(CalendarDate), Year(CalendarDate)-1) as FiscalYear,
Ceil(Month(CalendarDate)/3) as FiscalQuarter
RESIDENT TempCalendar;
Useful for reporting on non-calendar fiscal periods.
Answer:
Allows users to expand/collapse hierarchical levels dynamically in charts.
Example:
Country → State → City
Steps:
Create Drill-Down Dimension in chart.
Add multiple levels.
Users can click to see deeper level details.
Answer:
Use Alternate States for independent selection.
Assign charts and list boxes to separate states.
Example:
Sum({[StateA]<Year={2024}>} Sales) - Sum({[StateB]<Year={2024}>} Sales)
Helps in side-by-side comparison dashboards.
Answer:
Enable Partial Load Debugging.
Use Trace statements to log script execution.
Test each LOAD statement individually.
Use QlikView Table Viewer to inspect associations.
Avoid loading entire large datasets at once.
Answer:
Use REST connectors to fetch JSON or XML data.
Use ODBC/OLEDB for direct database access.
Use scheduled QVD generation to feed other BI tools.
Ensures QlikView can act as a central data hub.
