Lesson 1Parsing Dates and Standardising: Parsing OrderDate into date/time, handling various date formats and time zonesLearn how to parse raw date and time fields into Qlik dual date values. Handle different source formats, time zones, and local issues while creating standard date fields that support consistent calendar logic for your analyses.
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 OptionsConnect to CSV files using data connections and the file wizard. Set up paths, encodings, delimiters, and header options, and adjust LOAD statements to ensure clean, predictable imports without errors.
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 StatementLearn how to document Qlik load scripts with meaningful comments, headers, and section markers so future developers and auditors can quickly understand the logic, data 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 OrderUnderstand how Qlik evaluates script statements, from setting up connections to final STORE or EXIT. Learn about script sections, execution order, basic error handling, and how this affects data model results and performance.
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 FunctionsCreate derived calendar fields like Year, Month, and Quarter from OrderDate using Qlik date functions. Support flexible time analysis by generating extra flags and keys for fiscal and calendar views in your reports.
Extracting Year, Month, and DayBuilding Quarter and MonthNameUsing YearStart and MonthStartFiscal vs calendar date derivationsCreating date keys for link tablesLesson 6Text Cleaning in Script: TRIM, UPPER/LOWER, Replace, Null Handling and Standardising ProductCategory/ProductSubCategoryClean and standardise text fields in the script using TRIM, case functions, Replace, and null handling. Ensure ProductCategory and ProductSubCategory values are consistent for proper grouping and association in analyses.
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 ChecksDevelop a safe workflow for changing scripts, including partial reload, basics of incremental reload, and validation checks. Compare row counts and checksum values to confirm changes did not corrupt your 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 TypesLearn techniques to profile fields during load, checking distinct values, null rates, and inferred data types. Use temporary tables, RESIDENT loads, and system functions to validate data quality early in the process.
Counting distinct values per fieldDetecting nulls and empty stringsChecking inferred data typesSampling data with temporary tablesUsing script logs for profilingLesson 9Numeric Cleaning and Calculations in Script: SalesAfterDiscount, ProfitMargin with Division-by-Zero Guards and RoundingMaster numeric cleaning and calculations in script, including safe arithmetic, rounding, and guarding against division by zero. Implement measures like SalesAfterDiscount and ProfitMargin that work reliably in your 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 MaintainabilityApply best practices for script readability and maintainability using comments, logical sectioning, naming standards, and modular includes so teams can safely extend and troubleshoot complex ETL logic.
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 CasesExplore core Qlik script commands used to load and transform data. Understand when to use LOAD, SELECT, INLINE, RESIDENT, DROP, and RENAME, and how they combine to build efficient, maintainable 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 StrategiesHandle missing, negative, and outlier values with conditional expressions and NULLVALUE settings. Implement simple imputation and capping strategies that preserve data integrity while avoiding misleading metrics.
Configuring NULLVALUE and NullInterpretIF and Alt for conditional handlingTreating negative quantities and pricesSimple mean and median imputationsCapping extreme outliers in metrics
