Lesson 1Date Parsing and Standardising: Turning OrderDate into Date/Time, Dealing with Various Date Formats and Time ZonesDiscover how to turn raw date and time data into Qlik's dual date formats. Manage different input styles, time zones, and local settings while creating standard date fields that enable reliable calendar calculations for local business reports.
Using Date#, Timestamp#, and Time#Converting text dates to dual valuesHandling multiple source date formatsManaging time zones and UTC offsetsBuilding canonical OrderDate fieldsLesson 2Linking to and Bringing in CSV Files: File Paths, Encoding, Separators, and File Wizard ChoicesLink to CSV files via data links and the file guide. Set up paths, character sets, separators, and header choices, then tweak LOAD commands to guarantee smooth, expected imports for Zimbabwean sales data.
Creating and testing file connectionsUsing the data load file wizardConfiguring delimiters and quotesHandling headers and first data rowManaging encoding and locale issuesLesson 3Noting the Load Script: Ways to Add Clear Notes Explaining Each Major Section as Needed by the Project GuideFind out how to note Qlik load scripts with useful comments, headings, and part markers so that future team members and checkers can easily grasp the reasoning, data origins, and main business guidelines in the ETL flow.
Comment styles: single line and blockHeader blocks for script and major sectionsExplaining business rules in commentsTagging assumptions, TODOs, and risksLesson 4Grasping Qlik Sense and QlikView Load Script Setup and Running SequenceGrasp how Qlik processes script lines, from link setup to final STORE or EXIT. Learn about script parts, running order, basic error fixes, and how this sequence affects data model outcomes and speed in practical use.
Main, tabbed sections, and includesTop‑down execution and dependenciesControl statements: IF, FOR, DO WHILEError handling and script logsImpact of order on joins and fieldsLesson 5Making New Fields in Script: Year, Month, Quarter from OrderDate Using Qlik Date ToolsMake new calendar fields like Year, Month, and Quarter from OrderDate with Qlik date tools. Enable flexible time reviews by adding extra markers and keys for budget and standard calendar perspectives in local contexts.
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 Management and Standardising ProductCategory/ProductSubCategoryClean and uniform text fields in the script using TRIM, case changes, Replace, and null fixes. Make sure ProductCategory and ProductSubCategory entries are steady for grouping and linking in analytics.
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 Refining Script Updates: Reload Preview, Basic Incremental Reload, and Checking Row Numbers and Sum ChecksBuild a secure method for updating scripts, covering partial reloads, basic incremental reloads, and check steps. Match row numbers and sum values 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 Exploration and Checking During Load: Ways to Examine Unique Values, Nulls, and Data TypesLearn methods to check fields while loading, looking at unique values, null levels, and guessed data types. Employ temporary tables, RESIDENT loads, and system tools to confirm data quality from the start.
Counting distinct values per fieldDetecting nulls and empty stringsChecking inferred data typesSampling data with temporary tablesUsing script logs for profilingLesson 9Number Cleaning and Calculations in Script: SalesAfterDiscount, ProfitMargin with Zero-Division Protections and RoundingExpertise in number cleaning and calculations in scripts, including safe maths, rounding, and preventing zero-division errors. Set up measures like SalesAfterDiscount and ProfitMargin that work steadily in applications for business insights.
Cleaning numeric fields and coercionHandling nulls and nonnumeric valuesDivision‑by‑zero guards in formulasRounding and formatting measuresBuilding SalesAfterDiscount metricLesson 10Using Notes, Sections, and Best Ways in the Script for Easy Reading and UpkeepUse top practices for script clarity and upkeep with notes, logical sections, naming rules, and modular parts so groups can safely grow and fix complex ETL reasoning in team environments.
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 into main Qlik script commands for loading and changing data. Know when to apply LOAD, SELECT, INLINE, RESIDENT, DROP, and RENAME, and how they link to form efficient, sustainable ETL lines.
LOAD vs SELECT: when and whyUsing INLINE for small reference tablesRESIDENT loads for transformationsDROP and RENAME to tidy data modelPreceding LOAD patterns and benefitsLesson 12Managing Missing, Negative, and Unusual Values in Script: If-Then Expressions, NULLVALUE, and Basic Fill-In PlansDeal with missing, negative, and odd values using if-then expressions and NULLVALUE options. Apply basic fill-in and limit plans that keep data wholeness while steering clear of false measures.
Configuring NULLVALUE and NullInterpretIF and Alt for conditional handlingTreating negative quantities and pricesSimple mean and median imputationsCapping extreme outliers in metrics
