One business case, seven episodes. Build a complete Qlik model the way a real project grows: load the monthly files, clean the masters, value sales in two currencies, compare budget against actual, and ship a star schema.
1:52
Sales arrive as one file per month. The team wants all of them in one table, and next month's file should load without touching the script.
One RAW table that reads every monthly file through a wildcard and a path variable, with the project's number formats set once. The same idea over QVD files.
Starts from a new project.
1:26
Finance reconciles each month against its own file, so every row must say which file it came from.
A FOR EACH loop over FileList() that appends every file into one table, with the name of the file each row came from and a count of the rows read.
Starts from episode 1's project — Starting project (.qep).
1:39
Customers, products, salespeople and branches arrive with mixed casing, codes instead of names, and the same customer registered twice under a new ID.
Clean masters: a parameter read with Lookup, mapping tables, QUALIFY, cleaned fields, a survivor map for repeated customers, JOIN and KEEP.
Starts from episode 2's project — Starting project (.qep).
1:39
Most sales are in pesos, some in dollars. Every sale must be valued in pesos at the rate valid on its date.
Clean sales, an INNER JOIN as a business rule, IntervalMatch over exchange-rate ranges, amounts in pesos and amount bands.
Starts from episode 3's project — Starting project (.qep).
1:32
Management compares each branch's monthly budget with what it sold, after returns. The budget arrives with one column per month.
Returns as a preceding load, one movement table net of returns, a CROSSTABLE budget and an OUTER JOIN with the actual amount per branch and month.
Starts from episode 4's project — Starting project (.qep).
1:41
The app should load a clean star schema from QVDs, and the script must be ready for Dev, QA and Production.
Reload checks, keys with the Key Builder, a master calendar with holidays, facts and dimensions stored as QVDs, and debugging checkpoints you switch off in one click.
Starts from episode 5's project — Starting project (.qep).
1:16
A colleague left, and only the load script is left. Before anyone changes it, the team needs to see what it does.
The whole model again, read back from the finished script by Reverse Engineering, and its documentation exported.
Starts from the finished script, inside its zip.