Lesson 1Date Parsing and Standardisation: Parsing OrderDate into Date/Time, Managing Multiple Date Formats and Time ZonesDiscover how to parse raw date and time data into Qlik dual date formats. Deal with various source formats, time zones, and local settings while creating standard date fields that enable consistent calendar analysis for NZ applications.
Using Date#, Timestamp#, and Time#Converting text dates to dual valuesHandling multiple source date formatsManaging time zones and UTC offsetsBuilding standard OrderDate fieldsLesson 2Connecting to and Importing CSV Files: File Paths, Encoding, Delimiters, and File Wizard OptionsLink to CSV files via data connections and the file wizard. Set up paths, encodings, delimiters, and header choices, then tweak LOAD statements to guarantee smooth, predictable imports suitable for local data sources.
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 BriefMaster documenting Qlik load scripts with useful comments, headers, and section markers so that future developers and auditors can easily grasp the logic, data sources, and key business rules in the ETL process, aligning with NZ project standards.
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 setting up connections to final STORE or EXIT. Explore script sections, execution sequence, basic error handling, and how this affects data model outcomes and performance in Kiwi environments.
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 derived calendar fields like Year, Month, and Quarter from OrderDate using Qlik date functions. Enable flexible time analysis by producing extra flags and keys for fiscal and calendar perspectives relevant to NZ reporting.
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 Standardising ProductCategory/ProductSubCategoryClean and standardise text fields in the script with TRIM, case functions, Replace, and null management. Make sure ProductCategory and ProductSubCategory values are uniform for grouping and linking in local datasets.
Removing spaces with TRIM and PURGECHARUPPER, LOWER, and PROPER case usageReplacing bad or legacy text valuesHandling null and empty string casesStandardising product category labelsLesson 7Testing and Iterating Script Changes: Reload Preview, Incremental Reload Basics, and Validating Row Counts and Checksum ChecksBuild a secure workflow for modifying scripts, covering partial reloads, basic incremental reloads, and validation steps. Match row counts and checksums to verify that updates haven't damaged the data integrity.
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 TypesAcquire methods to profile fields while loading, examining unique values, null rates, and data types. Employ temporary tables, RESIDENT loads, and system functions to check data quality from the start in NZ contexts.
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 RoundingExcel in numeric cleaning and calculations in scripts, featuring safe maths, rounding, and protection from division by zero. Develop metrics like SalesAfterDiscount and ProfitMargin that perform steadily in applications for Kiwi businesses.
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 MaintainabilityImplement best practices for script clarity and upkeep using comments, logical sections, naming norms, and modular includes, allowing teams to extend and debug intricate ETL logic safely in a New Zealand team environment.
Organising 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 CasesDelve into essential Qlik script commands for loading and transforming data. Know when to apply LOAD, SELECT, INLINE, RESIDENT, DROP, and RENAME, and how they integrate to form efficient, sustainable ETL pipelines for local use.
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, and outlier values using conditional expressions and NULLVALUE options. Apply basic imputation and capping methods that maintain data integrity without producing deceptive metrics in NZ analytics.
Configuring NULLVALUE and NullInterpretIF and Alt for conditional handlingTreating negative quantities and pricesSimple mean and median imputationsCapping extreme outliers in metrics
