Lesson 1Date pars an' canonicalization: pars OrderDate inna date/time, handlin' multiple date formats an' time zonesLearn fi parse raw date an' time fields inna Qlik dual date values. Handle multiple source formats, time zones, an' locale issues while buildin' canonical date fields dat support consistent calendar logic fi betta analysis.
Using Date#, Timestamp#, and Time#Converting text dates to dual valuesHandling multiple source date formatsManaging time zones and UTC offsetsBuilding canonical OrderDate fieldsLesson 2Connectin' to an' importin' CSV files: file paths, encodin', delimiters, an' file wizard optionsConnect to CSV files usin' data connections an' di file wizard. Configure paths, encodin', delimiters, an' header options, an' adjust LOAD statements fi ensure clean, predictable imports wid ease.
Creating and testing file connectionsUsing the data load file wizardConfiguring delimiters and quotesHandling headers and first data rowManaging encoding and locale issuesLesson 3Documentin' di load script: how fi write clear comments fi explain each main part as required by di project statementLearn how fi document Qlik load scripts wid meaningful comments, headers, an' section markers so future developers an' auditors can quickly understand logic, data sources, an' key business rules defined inna di ETL process.
Comment styles: single line and blockHeader blocks for script and major sectionsExplaining business rules in commentsTagging assumptions, TODOs, and risksLesson 4Understandin' Qlik Sense an' QlikView load script structure an' execution orderUnderstand how Qlik evaluates script statements, from connection setup to final STORE or EXIT. Learn script sections, execution order, error handlin' basics, an' how dis flow impacts data model results an' 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 5Creatin' derived fields inna script: Year, Month, Quarter from OrderDate wid Qlik date functionsCreate derived calendar fields such as Year, Month, an' Quarter from OrderDate usin' Qlik date functions. Support flexible time analysis by generatin' additional flags an' keys fi fiscal an' calendar views.
Extracting Year, Month, and DayBuilding Quarter and MonthNameUsing YearStart and MonthStartFiscal vs calendar date derivationsCreating date keys for link tablesLesson 6Text cleanin' inna script: TRIM, UPPER/LOWER, Replace, Null handlin' an' standardizin' ProductCategory/ProductSubCategoryClean an' standardize text fields inna di script usin' TRIM, case functions, Replace, an' null handlin'. Ensure ProductCategory an' ProductSubCategory values are consistent fi groupin' an' association.
Removing spaces with TRIM and PURGECHARUPPER, LOWER, and PROPER case usageReplacing bad or legacy text valuesHandling null and empty string casesStandardizing product category labelsLesson 7Testin' an' iteratin' script changes: reload preview, incremental reload basics, an' validatin' row counts an' checksum checksDevelop a safe workflow fi changin' scripts, includin' partial reload, incremental reload basics, an' validation checks. Compare row counts an' checksum values fi confirm dat changes nuh corrupt 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 an' profilin' durin' load: methods fi inspect distinct values, nulls, an' data typesLearn techniques fi profile fields durin' load, checkin' distinct values, null rates, an' inferred data types. Use temporary tables, RESIDENT loads, an' system functions fi validate data quality early.
Counting distinct values per fieldDetecting nulls and empty stringsChecking inferred data typesSampling data with temporary tablesUsing script logs for profilingLesson 9Numeric cleanin' an' calculations inna script: SalesAfterDiscount, ProfitMargin wid division-by-zero guards an' roundin'Master numeric cleanin' an' calculations inna script, includin' safe arithmetic, roundin', an' guardin' against division by zero. Implement measures like SalesAfterDiscount an' ProfitMargin dat behave reliably inna apps.
Cleaning numeric fields and coercionHandling nulls and nonnumeric valuesDivision‑by‑zero guards in formulasRounding and formatting measuresBuilding SalesAfterDiscount metricLesson 10Usin' comments, sectionin', an' best practices inna di script fi readability an' maintainabilityApply best practices fi script readability an' maintainability usin' comments, logical sectionin', namin' standards, an' modular includes so teams can safely extend an' 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 an' dem common use casesExplore core Qlik script commands used fi load an' transform data. Understand when fi use LOAD, SELECT, INLINE, RESIDENT, DROP, an' RENAME, an' how dem combine fi 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 12Handlin' missin', negative, an' outlier values inna script: conditional expressions, NULLVALUE, an' simple imputation strategiesHandle missin', negative, an' outlier values wid conditional expressions an' NULLVALUE settings. Implement simple imputation an' cappin' strategies dat preserve data integrity while avoidin' misleading metrics.
Configuring NULLVALUE and NullInterpretIF and Alt for conditional handlingTreating negative quantities and pricesSimple mean and median imputationsCapping extreme outliers in metrics
