Lesson 1Date Parsing and Standardising: Parsing OrderDate into Date/Time, Managing Various Date Formats and Time ZonesLearn how to parse raw date and time data into Qlik's dual date formats. Deal with different source formats, time zones, and local settings while creating standard date fields that enable consistent calendar use across 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 ChoicesConnect to CSV files via data links and the file wizard. Set up paths, encodings, delimiters, and header settings, then tweak LOAD statements to guarantee smooth and expected 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: Writing Clear Comments to Explain Each Main Section as Needed by the ProjectLearn to add useful comments, headers, and section labels to Qlik load scripts so that other developers and reviewers in Botswana can easily follow the logic, data origins, and important business rules in the ETL steps.
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 SequenceGrasp how Qlik processes script commands, starting from connections to final STORE or EXIT. Explore script parts, run order, basic error fixes, and how this affects data models and speed in practical setups.
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 Using Qlik Date FunctionsMake calendar fields like Year, Month, and Quarter from OrderDate with Qlik's date tools. Enable flexible time reviews by adding flags and keys for both fiscal and standard calendar perspectives in local business needs.
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 text data in scripts using TRIM, case changes, Replace, and null fixes. Make sure ProductCategory and ProductSubCategory are uniform for proper grouping and linking in analyses relevant to Botswana markets.
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 Checking Row Counts and ChecksumsBuild a secure way to update scripts, covering partial reloads, basic incremental loads, and checks. Match row numbers and checksums to verify changes 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: Ways to Check Unique Values, Nulls, and Data TypesMaster methods to profile fields while loading, looking at unique values, null levels, and data types. Use temp tables, RESIDENT loads, and system tools to spot quality issues 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 Protection and RoundingHandle numeric cleaning and maths in scripts, including safe operations, rounding, and zero-division guards. Create reliable measures like SalesAfterDiscount and ProfitMargin for consistent app performance.
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 Easy Reading and UpkeepFollow top tips for script clarity and long-term care with comments, logical sections, naming rules, and modular parts so teams in Botswana can expand and fix complex ETL setups 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 Usual ApplicationsLook at main Qlik script commands for loading and changing data. Know when to apply LOAD, SELECT, INLINE, RESIDENT, DROP, and RENAME to form efficient, easy-to-maintain ETL flows.
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 Checks, NULLVALUE, and Basic Fill StrategiesManage missing, negative, and odd values using conditions and NULLVALUE options. Apply simple filling and limit tactics to keep data true while steering clear of false results in reports.
Configuring NULLVALUE and NullInterpretIF and Alt for conditional handlingTreating negative quantities and pricesSimple mean and median imputationsCapping extreme outliers in metrics
