Lesson 1Date parsing and canonicalization: parsing OrderDate into date/time, handling multiple date formats and time zonesMaster parsing raw date and time fields into Qlik dual date values. Manage various source formats, time zones, and locale challenges while creating standard date fields for uniform calendar analysis.
Using Date#, Timestamp#, and Time#Converting text dates to dual valuesHandling multiple source date formatsManaging time zones and UTC offsetsBuilding canonical OrderDate fieldsLesson 2Connecting to and importing CSV files: file paths, encoding, delimiters, and file wizard optionsLink to CSV files via data connections and file wizard. Set up paths, encodings, delimiters, and header settings, tweaking LOAD statements for smooth, reliable imports.
Creating and testing file connectionsUsing the data load file wizardConfiguring delimiters and quotesHandling headers and first data rowManaging encoding and locale issuesLesson 3Documenting the load script: how to write clear comments to explain each main part as required by the project statementDocument Qlik load scripts with useful comments, headers, and section labels so future developers and reviewers can easily grasp the logic, sources, and key business rules in the ETL process.
Comment styles: single line and blockHeader blocks for script and major sectionsExplaining business rules in commentsTagging assumptions, TODOs, and risksLesson 4Understanding Qlik Sense and QlikView load script structure and execution orderGrasp how Qlik processes script statements from connection to final STORE or EXIT. Understand script sections, execution sequence, basic error handling, and impacts on data model and speed.
Main, tabbed sections, and includesTop‑down execution and dependenciesControl statements: IF, FOR, DO WHILEError handling and script logsImpact of order on joins and fieldsLesson 5Creating derived fields in script: Year, Month, Quarter from OrderDate with Qlik date functionsGenerate calendar fields like Year, Month, Quarter from OrderDate using Qlik date functions. Enable versatile time analysis with extra flags and keys for fiscal and standard calendar views.
Extracting Year, Month, and DayBuilding Quarter and MonthNameUsing YearStart and MonthStartFiscal vs calendar date derivationsCreating date keys for link tablesLesson 6Text cleansing in script: TRIM, UPPER/LOWER, Replace, Null handling and standardizing ProductCategory/ProductSubCategoryClean and uniform text fields in script with TRIM, case functions, Replace, and null management. Ensure consistent ProductCategory and ProductSubCategory for proper grouping and linking.
Removing spaces with TRIM and PURGECHARUPPER, LOWER, and PROPER case usageReplacing bad or legacy text valuesHandling null and empty string casesStandardizing product category labelsLesson 7Testing and iterating script changes: reload preview, incremental reload basics, and validating row counts and checksum checksBuild a secure workflow for script updates with partial reloads, incremental basics, and checks. Match row counts and checksums to verify changes haven't spoilt the data.
Using limited load for quick testsBasics of incremental reload logicValidating row counts by tableChecksum and hash‑based comparisonsRolling back and versioning scriptsLesson 8Field discovery and profiling during load: methods to inspect distinct values, nulls, and data typesTechniques to profile fields on load, reviewing unique values, nulls, and data types. Employ temp tables, RESIDENT loads, and functions to check data quality early on.
Counting distinct values per fieldDetecting nulls and empty stringsChecking inferred data typesSampling data with temporary tablesUsing script logs for profilingLesson 9Numeric cleansing and calculations in script: SalesAfterDiscount, ProfitMargin with division-by-zero guards and roundingHandle numeric cleaning and calculations in script with safe maths, rounding, and zero-division protection. Create reliable SalesAfterDiscount and ProfitMargin for apps.
Cleaning numeric fields and coercionHandling nulls and nonnumeric valuesDivision‑by‑zero guards in formulasRounding and formatting measuresBuilding SalesAfterDiscount metricLesson 10Using comments, sectioning, and best practices in the script for readability and maintainabilityFollow best practices for script clarity and upkeep with comments, sections, naming, and modular includes, helping teams extend and fix complex ETL logic safely.
Organizing tabs and logical sectionsConsistent field and table namingUsing include files for modularitySeparating staging and model layersRefactoring duplicated script logicLesson 11Basic script commands: LOAD, SELECT, INLINE, RESIDENT, DROP, RENAME and their common use casesKey Qlik script commands for loading and transforming data. Know when to apply LOAD, SELECT, INLINE, RESIDENT, DROP, RENAME for efficient ETL pipelines.
LOAD vs SELECT: when and whyUsing INLINE for small reference tablesRESIDENT loads for transformationsDROP and RENAME to tidy data modelPreceding LOAD patterns and benefitsLesson 12Handling missing, negative, and outlier values in script: conditional expressions, NULLVALUE, and simple imputation strategiesManage missing, negative, outlier values with conditions and NULLVALUE. Use basic imputation and limits to keep data sound without false metrics.
Configuring NULLVALUE and NullInterpretIF and Alt for conditional handlingTreating negative quantities and pricesSimple mean and median imputationsCapping extreme outliers in metrics
