QSDA cheat sheet

Association is created automatically when two tables share a field with the identical name; field names are matched case-insensitively, so "CustomerID" and "customerid" associate.

Synthetic key: generated when two or more fields form a composite link between tables; Qlik builds an extra implicit key table, inflating data volume and slowing the model - replace with a concatenated or AutoNumberHash128 composite key, or a link table.

Circular reference: three or more tables linked in a loop; resolve with a link table, by renaming/aliasing one key so the loop breaks, or by removing the redundant association.

Star schema: one central fact table joined to flat dimension tables on single keys; the lowest association count and usually the fastest model.

Snowflake schema: dimensions normalized into sub-dimension tables; logically correct but adds joins and associations compared with a star.

Join types: Join (full outer on matching keys), Inner Join, Left Join, Right Join, Outer Join (full), Cross Join (cartesian product).

Keep prefix: filters the loaded table to rows whose key already exists in the resident table, without merging the tables; Where Exists(field) is the equivalent filter.

Concatenate: appends rows to a table with identical field structure; NOConcatenate suppresses automatic concatenation, FORCE Concatenate overrides mismatched field names.

Qualify prefixes every field name in the following LOAD with its table name to prevent accidental associations on generic names such as ID or Date; Unqualify reverses it.

Preceding load: LOAD statements are stacked and the upper statement reads the result of the LOAD below it; execution order is bottom-up, so one source read can feed several transformation steps.

Resident load reads from a table already in memory, so the source is not queried twice and aggregations can be built in successive steps.

ApplyMap(value, mappingTable [, default]) returns the mapped value; unmatched values return the input unchanged unless a third default argument is supplied.

Mapping LOAD creates a two-column mapping table that stays in memory but is not part of the data model and never associates with other tables.

Incremental load pattern: read the maximum key or timestamp from the stored QVD, extract only newer rows, concatenate them with the QVD, then STORE the merged table back.

Buffer [name] caches an expensive database LOAD to a local file for a defined number of reloads, shielding the source from repeated full queries.

Binary [file] loads another app's complete data model without executing its script; the loaded tables cannot be modified afterwards.

Extract once into QVD and load QVDs downstream: a QVD load is roughly an order of magnitude faster than the equivalent SQL query.

Where Exists(field) filters the incoming table to keys already present in memory - the standard filter for incremental extraction.

Null(), IsNull() and Alt(a,b,c) (first non-null argument) are the core null-handling functions; Coalesce() returns the first non-null expression.

NullAsValue field1, field2; converts NULLs to an actual value so they appear as a selectable dimension value; NullAsNull *; restores default behaviour.

Standard cleansing functions: Trim(), LTrim(), RTrim(), PurgeChar(), KeepChar(), Upper(), Lower(), Capitalize(), Replace().

Keys must share the same data type and format before associating; harmonize with Num#(), Date#(), Text() or ApplyMap, otherwise the association silently fails.

Granularity is the set of key fields that defines a table's grain; joining tables of different grain multiplies rows and inflates every sum and count.

Orphan keys break referential integrity; detect them with Exists() or by counting unassociated dimension values before publishing.

Cartesian product: two tables with no common field produce the full combination of rows - with three or more tables, always verify the association chain.

IntervalMatch links discrete values to numeric intervals and returns a synthetic composite key that must be dropped after the match is made.

Reduce volume as early as possible: a preceding load listing only required fields, a WHERE filter on the source, and DROP FIELD once a field is no longer needed.

AutoNumber() and AutoNumberHash128() convert repeated text keys into integers, shrinking the symbol table and speeding associations and aggregations.

Set analysis is evaluated once per chart object; row-level if() is evaluated per row and should be replaced by script flags or set expressions.

QVD is columnar and compressed and carries an XML header with load and field metadata; reading it avoids re-querying the source system.

A QVD load stays optimized only when the LOAD * FROM file.qvd form is kept - renaming fields, calculating fields, or adding WHERE forces a non-optimized load.

Avoid SELECT *: every extra column adds symbol-table memory and widens the record, and unused columns are usually never dropped.

Synthetic keys and circular references expand row counts multiplicatively; removing them is often the largest single performance gain in a model.

Aggregate in the script with GROUP BY or a resident load rather than aggregating millions of rows inside a chart expression.

Long reloads usually trace back to joins against large tables, text keys, or repeated source queries - profile by timing each LOAD block in the log.

SECTION ACCESS; defines the security table and must be closed with a later SECTION APPLICATION; statement before the data model resumes.

Mandatory section access fields: ACCESS (ADMIN or USER) and USERID (or USER); PASSWORD and GROUP are optional but commonly used.

At least one ACCESS=ADMIN row must exist, and the admin user must also be listed in the app's access rights, otherwise the app becomes unopenable.

A * in a section access field value grants that user all values of the field, so no reduction is applied on that column.

Data reduction is implicit: a USER row listing a field value restricts that user to records containing that value across the whole model.

OMIT fieldname removes a field entirely for the listed users, so it cannot be selected or displayed in any sheet.

Qlik Sense applies section access only when 'Initial data reduction based on section access' is enabled in the app's settings.

Store database credentials in QMC data connections under a dedicated service account; never hardcode user names or passwords in the load script.

Security rules in the QMC govern app, stream and object visibility; section access governs row-level data reduction inside the app.

Layered QVD architecture: L0 raw extract from source, L1 cleansed and conformed QVDs, L2 app-specific mart; published apps read only from L2.

Reload scheduling lives in the QMC task scheduler; triggers are time-based or chained to the completion of a predecessor task.

Published apps are read-only for consumers; changes are made in the source app and republished, which replaces the published copy.

Environment promotion: export the .qvf from Dev, import into Test then Prod through the QMC, keeping connections and section access environment-specific.

Data and folder connections are defined in the QMC and referenced in script via lib:// paths so the same script runs unchanged in every environment.

Reload failure diagnostics: read the task log in the QMC and the log files under %ProgramData%\Qlik\Sense\Log.

Incremental reload requires a stable key or timestamp column plus a stored QVD that holds the maximum value already processed.

QVD files must sit on a shared folder connection the Qlik Sense service account can write to; personal or user-profile paths break scheduled reloads.