QlikSense Data Architect Masterclass

Take a tour of the Qlik Sense desktop, exploring the hub, streams, and apps, and how the data model, load scripts, and data manager shape the application.

Understand the three-layer Qlik Sense architecture—data access, application, and presentation—with data sources from Excel to big data and in-memory calculations handled by the proxy, engine, scheduler, repository, and Qmk.

Unlock Qlik Sense Desktop via two methods: register with Click Branch for a PSD file, or use a cloud business subscription. The lecture guides installation, unlocking steps, and trial expiration.

Learn how to unlock Qlik Sense Desktop using Click Business Cloud authentication, including registering for a 30-day free trial, authenticating via the profile, and launching the browser-based desktop for training.

Set up the basic Qlik Sense bootcamp folder on your local machine, download the data and include folders from GitHub, and prepare the northwind database for building the data model.

Master the data load editor in the qliksense data architect masterclass, learn the data load types: data manager and data load editor, and use sections, connections, script editor, and debugging.

Connect, extract, and load to build a qlik sense data model, using lib connect, select statements, preceding load, and Microsoft Jet DB provider 32-bit connection to Northwind 2014 MDB.

Learn to load data in Qlik Sense by using the data selection panel to choose tables and fields, inserting load statements, and understanding top-down scripting with bottom-up execution, plus comments.

This challenge guides you to load fields from order detail table, remove the connection string, name the table with a colon, and reload the script in QlikSense data architect masterclass.

Load order detail fact table with the data dialog, remove redundant connect statement, and save the script; verify the data model to ensure field name matches and remains case sensitive.

QlikSense Data Architect Masterclass shows how to perform basic transformations by adding year, month, and day fields to orders table with a preceding load on a SQL select.

Apply basic transformation to create line sales amount in the order detail table by multiplying unit price, quantity, and (1 - discount), then reload and review data model for accuracy.

Add a new field line sales amount in the orders detail table as unit price times quantity times one minus discount. Reload and verify data model; correct day spelling.

Explore synthetic keys basics, how multiple common fields create a synthetic key and slow performance, and how to resolve it by removing or renaming fields, using qualify, or link tables.

Create a dimensions section, load the products table, and alias unit price to catalog price to resolve the synthetic key between orders, order details, and products.

Load categories, customers, and employees tables, triggering a synthetic key from shared fields. Resolve the synthetic key to keep the data model clean and note circular references for future lectures.

Resolve a synthetic key by renaming the customers table fields: address, city, country, postal code, and region, to avoid conflicts with employees; reload and confirm no synthetic keys remain.

Explore the data model viewer to inspect table relationships, use the toolbar and zoom to review fields, and apply information density and subset ratio to assess data quality.

Load an Excel file into QlikSense and create a flat file connection. Use a wildcard to auto concatenate multiple employee files with the same columns.

Create a new XML file connection, load the suppliers table from an XML source, and identify synthetic keys from multiple common fields; use the qualify statement to resolve them.

Set up an XML data connection, load the suppliers table from suppliers.xml, and use a qualify statement to handle synthetic keys like company name, contact name, title, fax, and phone.

Learn how the qualify statement prefixes each field with its table name to resolve synthetic keys between suppliers, employees, and customers, and avoid island tables by unqualifying at the end.

Qualify all fields with a qualify statement on the suppliers table, unqualify the supplier ID field, and use star unqualified to prevent synthetic keys, shaping the data model.

Master how variables in Qlik Sense store a single value, understand let vs set with evaluation versus string assignment, and use dollar sign expansion in load scripts.

Create an xml file path variable, add it to variables.txt in the include folder, apply dollar sign expansion, and reload the document to validate the data model.

Copy the excel path variable to create an xml file path, update the connection to xml, and apply dollar sign expansion in the prompt; reload to verify suppliers table loads.

explore include files that store path variables and connection strings, using must_include with dollar expansion to load a single shared text file across developers.

Create a variables.txt file in include folder, store the set v excel file path there, and include this file in the document using the same steps as connect_northwind include file.

Create a variables.tf include file in the include folder, save it as variables.txt, and load it to verify the include file connection.

Use a dedicated record counter in the orders table to ensure frequency on the order ID key field comes from the correct table, avoiding cross-table miscounts.

Determine which table should hold the active customer record counter, account for duplicate IDs, reload data, and build a straight table of active customers with their order counts.

Learn to create a customer ID counter in the orders table to count active customers accurately, then build a straight table showing active customers by year alongside the total orders.

Explore how the Qlik Sense where clause filters records during load statements, using variables and dollar sign expansion to cap data to last three years, e.g., year greater than 1996.

Create a new table with resident load from an existing RAM table, add columns, and drop the original to avoid synthetic keys.

Use the where exists clause to include every employee who contributed to sales by matching the current load's employee ID with the orders table's preloaded employee ID.

Tackle a where exists challenge by filtering order details for year greater than 1996, using identical field names to compare previous and current loads on order id and validate data.

Add a where exists clause to the order details table so the order id matches the filtered orders table (year > 1996), achieving a 100% subset ratio.

Master calendar fundamentals explain linking a date to a master calendar, creating time fields like month, quarter, year to date, and using dual date values and interpretation/formatting functions.

Derive min and max dates for a master calendar using the field value function on a date field, compare scanning a table with using a field, applying flags and floor.

Create a master calendar by deriving min and max dates, generating a date series, and building day, month, year, week, quarter, month-year, and year-to-date flags.

Learn the fundamentals of mapping tables in QlikSense: create two-field lookups with a key and attribute, apply map to the fact table, and rely on implicit cleanup of mappings.

Create a mapping table beside the main section using the Northwind shippers table, then apply map to add shipper name to the orders fact table.

Create a cost of goods sold field by multiplying unit price by quantity, using a product-to-orders mapping table.

Create a mapping table from the products table in the Northwind database, loading product ID and unit price. Apply mapping to the orders detail table, computing cost of goods sold.

Discover how preceding load works in Qlik Sense, stacking loads to compute margin as line sales amount minus cost of goods sold in orders detail, then reload to verify.

Use a preceding load to create margin as line sales amount minus cost of goods sold, preserve fields with an asterisk, and reload to verify in the orders detail table.

Explore how joins work in Qlik Sense, including associative and natural joins, and how left, right, inner, and full outer joins shape data models.

Learn how to optimize a QlikSense data model by using left joins and lap joins to consolidate categories into products, avoid cartesian joins, and boost performance.

Use the data load editor to perform a lap join between orders detail and the order detail extended table from Northwind, adding the extended price field to orders detail.

Master concatenation in Qlik Sense by learning how to add rows from multiple tables, distinguish implicit versus force concatenation, and handle matching or differing field names.

Explore implicit and forced concatenation in Qlik Sense through a practical employees example, showing when implicit concatenation occurs and how to force concatenation to avoid synthetic keys.

Load new products from an Excel file and concatenate them with the existing products table, then explore making the concatenation implicit in a QlikSense data model.

Master implicit concatenation by aligning fields in the products and new products tables, add the discontinued field, and rename unit price to catalog price before reloading.

Consolidate the orders and order details into a single fact table by lap joining order details to orders, rename tables to facts, and optimize the data model with auto layout.