Lesson 1Parsing Dates and Standardising: Turning OrderDate into date/time, dealing with different date styles and time areasLearn how to turn raw date and time info into Qlik dual date values. Manage various source formats, time areas, and local settings while creating standard date fields that allow steady calendar workings.
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 Bringing in CSV Files: File paths, coding, separators, and file wizard choicesLink to CSV files using data links and the file wizard. Set up paths, codings, separators, and header choices, and change LOAD statements to make sure imports are clean and expected.
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: How to write clear notes to explain each main part as needed by the project guideLearn to note Qlik load scripts with useful comments, headers, and section signs so future makers and checkers can quickly get 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 Setup and Run OrderGet how Qlik checks script statements, from link setup to final STORE or EXIT. Learn script parts, run order, basic error handling, and how this flow affects data model outcomes 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 5Making New Fields in Script: Year, Month, Quarter from OrderDate with Qlik Date ToolsMake new calendar fields like Year, Month, and Quarter from OrderDate using Qlik date tools. Help flexible time checks by making extra signs and keys for money and calendar views.
Extracting Year, Month, and DayBuilding Quarter and MonthNameUsing YearStart and MonthStartFiscal vs calendar date derivationsCreating date keys for link tablesLesson 6Cleaning Text in Script: TRIM, UPPER/LOWER, Replace, Null Handling and Standardising ProductCategory/ProductSubCategoryClean and standardise text fields in the script using TRIM, case tools, Replace, and null handling. Make sure ProductCategory and ProductSubCategory values are steady for 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 Repeating Script Changes: Reload Preview, Basic Incremental Reload, and Checking Row Counts and Checksum TestsBuild a safe way for changing scripts, including partial reload, basic incremental reload, and check steps. Compare row counts and checksum values to confirm changes did not spoil data.
Using limited load for quick testsBasics of incremental reload logicValidating row counts by tableChecksum and hash‑based comparisonsRolling back and versioning scriptsLesson 8Finding Fields and Checking During Load: Ways to Look at Unique Values, Nulls, and Data TypesLearn ways to check fields during load, looking at unique values, null rates, and guessed data types. Use temp tables, RESIDENT loads, and system tools to check data quality early.
Counting distinct values per fieldDetecting nulls and empty stringsChecking inferred data typesSampling data with temporary tablesUsing script logs for profilingLesson 9Cleaning Numbers and Calculations in Script: SalesAfterDiscount, ProfitMargin with Zero Division Guards and RoundingMaster cleaning numbers and calculations in script, including safe maths, rounding, and guarding against zero division. Make measures like SalesAfterDiscount and ProfitMargin that work well in apps.
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 Keeping UpUse best ways for script easy reading and keeping up with notes, logical sections, naming rules, and modular includes so teams can safely add to and fix 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 UsesLook at main Qlik script commands for loading and changing data. Get when to use LOAD, SELECT, INLINE, RESIDENT, DROP, and RENAME, and how they join to build good, keepable 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 12Handling Missing, Negative, and Odd Values in Script: If Statements, NULLVALUE, and Simple Fill StrategiesHandle missing, negative, and odd values with if statements and NULLVALUE settings. Make simple fill and cap strategies that keep data whole while avoiding wrong measures.
Configuring NULLVALUE and NullInterpretIF and Alt for conditional handlingTreating negative quantities and pricesSimple mean and median imputationsCapping extreme outliers in metrics
