QlikSense ETL Analysis - Part 6: Functions & Techniques Glossary

Section 1: Data Loading Operations

1.1 LOAD Statement

Purpose: Loads data from various sources into QlikSense data model

Syntax:

LOAD FieldName1, FieldName2, ...
FROM source;

Project Example (from Transactions.txt, line 68):

LOAD
    APPLYMAP('mAccFramework',ACCOUNTING_FRAMEWORK_KEY,'-') AS "Accounting Framework Transaction",
    TEXT("Bus Trans No") AS "Bus Trans No",
    "Current Value Myr",
    "Current Value Qc"
FROM [$(LIB_FOLDER)/$(DWHMODEL)/Transactions/Transactions.qvd] (qvd);

Explanation:

Loads specific fields from a QVD file
Can apply functions (APPLYMAP, TEXT) during load
Can rename fields using AS
Variable expansion $(LIB_FOLDER) for dynamic paths

When to Use:

Loading data from QVD, Excel, CSV, database
Creating calculated fields during load
Renaming fields

1.2 MAPPING LOAD

Purpose: Creates a mapping table (key-value pair) for efficient lookups using APPLYMAP

Syntax:

[MapName]:
MAPPING LOAD KeyField, ValueField
FROM source;

Project Example (from Mappings.txt, line 12-14):

[mBusTransCode]:
MAPPING LOAD BUS_TRANS_CATEGORY_KEY, "Business Transaction Category Bus Trans Code" 
RESIDENT TmpBCat;

[mBusTransCodeName]:
MAPPING LOAD BUS_TRANS_CATEGORY_KEY, "Business Transaction Category Bus Trans Code Name" 
RESIDENT TmpBCat;

Explanation:

Creates a 2-column lookup table (Key → Value)
Much faster than JOIN for lookups
Cannot be used as regular table (only for APPLYMAP)
Common pattern: Load temp table → Create multiple mappings → Drop temp table

When to Use:

When you need to lookup values based on a key (like VLOOKUP in Excel)
When same lookup is needed multiple times
To avoid expensive JOINs

Best Practice from Project:

// Step 1: Load temporary table with all fields
TmpBCat:
LOAD BUS_TRANS_CATEGORY_KEY, "Field1", "Field2", "Field3"
FROM [Source.qvd] (qvd);

// Step 2: Create multiple mappings from same temp table
[mMap1]: MAPPING LOAD KEY, "Field1" RESIDENT TmpBCat;
[mMap2]: MAPPING LOAD KEY, "Field2" RESIDENT TmpBCat;
[mMap3]: MAPPING LOAD KEY, "Field3" RESIDENT TmpBCat;

// Step 3: Drop temp table to save memory
DROP TABLE TmpBCat;

1.3 RESIDENT

Purpose: Loads data from a table already loaded into QlikSense memory

Syntax:

LOAD Field1, Field2
RESIDENT ExistingTableName;

Project Example (from Transactions.txt, line 5-7):

[Transactions]:
LOAD *
  , ((MATCH([Instrument Type], 'Equity','GDR/ADR','Right','Warrant', 'Covered warrant') 
      OR ([Instrument Type]='Fund certificate' AND MATCH([Security Type], 'ETF', 'ETP', 'ETN', 'REIT'))
    ) and MATCH([Buy or Sell], 'Buy','Sell')) AS IsS2
;
LOAD *,
    num([Trade Date]) as MAIN_DATE_KEY,
    ...
FROM [Transactions.qvd] (qvd);

Explanation:

First LOAD reads from second LOAD (stacked loads)
Allows multi-step transformations
Data stays in memory (no disk I/O after initial load)

When to Use:

Adding calculated fields to existing table
Filtering or aggregating already-loaded data
Creating dimension tables from fact tables

Project Example (from Grouped_Total.txt):

[Instrument Name]:
LOAD DISTINCT 
    "Security ID", 
    "Instrument" AS "Instrument Name"
RESIDENT [Transactions];

CONCATENATE ([Instrument Name]) 
LOAD 
    "Security ID", 
    'TOTAL' AS "Instrument Name"
RESIDENT [Transactions];

1.4 FROM (QVD Files)

Purpose: Loads data from QlikView Data files (optimized binary format)

Syntax:

LOAD Field1, Field2
FROM [FilePath.qvd] (qvd);

Project Example (from Transactions.txt, line 189-192):

FROM [$(LIB_FOLDER)/$(DWHMODEL)/Transactions/Transactions.qvd] (qvd)
WHERE 1=1
  AND [Transaction Cancellation No] = 0
  AND [Transaction Status Level No] >= 30;

Explanation:

QVD files are QlikSense's native compressed binary format
10-100x faster than reading from database or CSV
Variables $(LIB_FOLDER) and $(DWHMODEL) make paths dynamic
Can apply WHERE filter during load

When to Use:

Primary data source in most QlikSense ETL
Incremental loading (load only new records)
Data archiving and backup

1.5 INLINE Data

Purpose: Creates small tables with hardcoded data directly in script

Syntax:

LOAD * INLINE [
Field1, Field2
Value1, Value2
Value3, Value4
];

Project Example (from Monthly_Aggregations.txt):

LEFT KEEP ([Transactions]) LOAD * INLINE
[Month Name w Total
JAN
FEB
MAR
APR
MAY
JUN
JUL
AUG
SEP
OCT
NOV
DEC
TOTAL
];

Explanation:

Creates dimension table with 13 rows (12 months + TOTAL)
LEFT KEEP ensures only months present in Transactions are kept
Used for creating fixed dimension values

When to Use:

Small lookup tables (status codes, categories)
Test data
Fixed dimension values (months, quarters)
Configuration tables

1.6 AUTOGENERATE

Purpose: Generates rows automatically without a data source

Syntax:

LOAD Expression
AUTOGENERATE NumberOfRows;

Project Example (from Master_Calendar.txt, line 36-39):

LOAD 
    Date(MinDate + IterNo()) AS TempDate
WHILE MinDate + IterNo() <= MaxDate;

LOAD
    min(FieldValue('Trade Date', recno()))-1 AS MinDate,
    max(FieldValue('Trade Date', recno())) AS MaxDate
AUTOGENERATE FieldValueCount('Trade Date');

Explanation:

First LOAD: Uses IterNo() in WHILE loop to generate dates
Second LOAD: AUTOGENERATE creates rows to iterate through field values
Pattern generates complete date range between min and max trade dates

When to Use:

Generating date ranges (calendars)
Creating sequence numbers
Generating test data
Creating fixed-size dimension tables

Section 2: Table Joining & Association

2.1 LEFT JOIN

Purpose: Joins all rows from left table with matching rows from right table (keeps all left rows)

Syntax:

LEFT JOIN (LeftTableName)
LOAD Field1, Field2
FROM source;

Project Example (from Transactions.txt, line 239-243):

LEFT JOIN (Transactions)
[Open Payments]:
LOAD DISTINCT
    [Transaction Number Ik] AS "Transaction Ik",
    [Outstanding Amount Pc] AS "Outstanding Amount Pc",
    [Outstanding Amount Qc] AS "Outstanding Amount Qc"
FROM [$(LIB_FOLDER)/$(DWHMODEL)/Transactions Open Payments/Transactions Open Payments.qvd] (qvd);

Explanation:

Adds outstanding payment amounts to Transactions table
If Transaction has no open payment → Outstanding Amount = NULL
Join key: "Transaction Ik" (must exist in both tables)
All transactions are kept, even those without open payments

When to Use:

Enriching main table with additional fields
When you want to keep all records from main table
Adding optional attributes

2.2 RIGHT JOIN

Purpose: Joins all rows from right table with matching rows from left table (keeps all right rows)

Syntax:

RIGHT JOIN (LeftTableName)
LOAD Field1, Field2
FROM source;

Project Example (from Capital_Info.txt, line 9-13):

RIGHT JOIN ([Capital Info])
LOAD
    SECURITY_KEY,
    MAX("Share Date Full") AS "Share Date Full"
RESIDENT [Capital Info]
WHERE "Share Date Full" <= "Trade Date"
GROUP BY SECURITY_KEY;

Explanation:

Keeps only the most recent "Share Date" for each security before trade date
Right table (second LOAD) determines which rows survive
Essentially filters [Capital Info] to most recent effective date

When to Use:

Filtering to most recent/relevant records
Keeping only matching subset

2.3 INNER JOIN

Purpose: Keeps only rows that exist in BOTH tables

Syntax:

INNER JOIN (LeftTableName)
LOAD Field1, Field2
FROM source;

Project Example (from PFC_at_Lodgement.txt, line 79-82):

INNER JOIN LOAD QUOTATION_CURRENCY_KEY,
    [Quotation Currency] AS [Quotation Currency PFC]
FROM [$(LIB_FOLDER)/$(DWHMODEL)/Portfolio Calculation Results/Quotation Currency.qvd] (qvd);

Explanation:

Adds currency names to PFC_Holdings
Only keeps holdings that have a matching currency record
Filters out any orphan records

When to Use:

Ensuring referential integrity
Filtering to only valid combinations
Removing orphan records

2.4 CONCATENATE

Purpose: Appends rows from one table to another (union)

Syntax:

CONCATENATE (TargetTable)
LOAD Field1, Field2
FROM source;

Project Example (from Grouped_Total.txt):

[Instrument Name]:
LOAD DISTINCT 
    "Security ID", 
    "Instrument" AS "Instrument Name"
RESIDENT [Transactions];

CONCATENATE ([Instrument Name]) 
LOAD 
    "Security ID", 
    'TOTAL' AS "Instrument Name"
RESIDENT [Transactions];

Explanation:

First LOAD: Gets all actual instrument names
CONCATENATE: Adds 'TOTAL' rows for each security
Result: Dimension table with actual instruments + TOTAL for aggregation

When to Use:

Combining data from multiple sources
Adding "TOTAL" or "ALL" rows to dimensions
Historical + incremental data

2.5 LEFT KEEP

Purpose: Reduces left table to only rows that have matches in right table (association, not join)

Syntax:

LEFT KEEP (LeftTableName)
LOAD Field1, Field2
FROM source;

Project Example (from Transactions.txt, line 219-227):

LEFT KEEP (Transactions)
[Transactions Profit Loss]:
LOAD DISTINCT
    APPLYMAP('mAccFramework',ACCOUNTING_FRAMEWORK_KEY,'-') AS "Accounting Framework PL",
    "Booked Current Value Pc",
    "Cost Value Myr",
    "Transaction Ik"
FROM [$(LIB_FOLDER)/$(DWHMODEL)/Transactions Profit Loss/Transactions Profit Loss.qvd] (qvd)
WHERE "Accounting Framework Ik" > 0;

Explanation:

Keeps only P&L records that have matching Transaction
Creates separate P&L table (not merged into Transactions)
Tables remain separate but associated through "Transaction Ik"
More memory efficient than JOIN

When to Use:

When you need filtered association, not merged data
Memory optimization (avoid wide tables)
Maintaining table granularity

2.6 RIGHT KEEP

Purpose: Reduces right table to only rows that have matches in left table

Syntax:

RIGHT KEEP (RightTableName)
LOAD Field1, Field2
FROM source;

Project Example (from Monthly_Aggregations.txt):

LEFT KEEP ([Transactions]) LOAD * INLINE
[Month Name w Total
JAN
FEB
...
TOTAL
];

When to Use:

Filtering dimension tables to only used values
Removing unused master data

Section 3: Data Filtering & Control

3.1 WHERE Clause

Purpose: Filters rows during data load based on conditions

Syntax:

LOAD Field1, Field2
FROM source
WHERE Condition;

Project Example (from Transactions.txt, line 189-192):

FROM [$(LIB_FOLDER)/$(DWHMODEL)/Transactions/Transactions.qvd] (qvd)
WHERE 1=1
  AND [Transaction Cancellation No] = 0
  AND [Transaction Status Level No] >= 30;

Explanation:

WHERE 1=1 : Always true, makes adding AND conditions easier
Loads only active transactions (not cancelled)
Loads only confirmed transactions (status >= 30)
Filter applied DURING load (more efficient)

When to Use:

Filtering source data to reduce memory
Loading incremental data
Excluding invalid/cancelled records
Loading specific subsets

3.2 GROUP BY

Purpose: Aggregates data by grouping rows with same key values

Syntax:

LOAD 
    KeyField,
    SUM(ValueField) as Total
RESIDENT SourceTable
GROUP BY KeyField;

Project Example (from Capital_Info.txt, line 9-13):

RIGHT JOIN ([Capital Info])
LOAD
    SECURITY_KEY,
    MAX("Share Date Full") AS "Share Date Full"
RESIDENT [Capital Info]
WHERE "Share Date Full" <= "Trade Date"
GROUP BY SECURITY_KEY;

Explanation:

Groups by SECURITY_KEY (each security gets one row)
Finds MAX(Share Date) for each security
Result: Most recent share capital info for each security

When to Use:

Getting latest/earliest records
Summarizing detail to aggregated level
Finding unique combinations
Creating summary tables

3.3 ORDER BY

Purpose: Sorts data during load

Syntax:

LOAD Field1, Field2
RESIDENT SourceTable
ORDER BY Field1 DESC, Field2 ASC;

Project Example (from Transactions.txt, line 325-328):

[TD2]:
LOAD 
    TDF AS [Trade Date Date Full], 
    RowNo() as RNo
RESIDENT TD
ORDER BY TDF;

Explanation:

Sorts dates in ascending order
Assigns sequential row numbers
Used for PEEK operations

When to Use:

When PEEK or PREVIOUS depends on order
Creating ranked lists

3.4 DISTINCT

Purpose: Removes duplicate rows from result

Syntax:

LOAD DISTINCT Field1, Field2
FROM source;

Project Example (from Capital_Info.txt, line 3-4):

LOAD DISTINCT SECURITY_KEY, [Trade Date] 
RESIDENT [Transactions];

Explanation:

Gets unique Security-Date combinations

When to Use:

Creating dimension tables from fact tables
Getting unique value lists
Removing duplicate records

Section 4: String Functions

4.1 UPPER / LOWER

Purpose: Converts text to uppercase or lowercase

Syntax:

UPPER(TextField)
LOWER(TextField)

Project Example (from Transactions.txt, line 110-111):

UPPER(APPLYMAP('mSecId',SECURITY_KEY,'-')) AS "Security ID",
UPPER(APPLYMAP('mSecName',SECURITY_KEY,'-')) AS "Security Name",

When to Use:

Standardizing text for matching
Display formatting
Case-insensitive comparisons

4.2 TEXT

Purpose: Converts any value to text/string format

Syntax:

TEXT(AnyValue)

Project Example (from Corporate_Actions.txt, line 3-4):

LEFT JOIN ([Transactions]) LOAD DISTINCT
    TEXT("Bus Trans No") AS "Bus Trans No",
    'Yes' AS "Corporate Action Flag",

Explanation:

Ensures "Bus Trans No" is treated as text (not number)
Prevents issues like losing leading zeros

When to Use:

Preserving leading zeros (like '00123')
Forcing numeric IDs to be text

4.3 REPLACE

Purpose: Replaces all occurrences of a substring with another string

Syntax:

REPLACE(SourceText, SearchString, ReplaceString)

Project Example (from Sheet_01.txt, line 59):

LET xSHEET01_Entitlement = REPLACE('Sum({$(xFILTER_SHEET01)} [Nominal Basis])','@','$');

Explanation:

REPLACE('@', '$') converts placeholder @ to dollar sign $
Allows using @ in string ($ would trigger variable expansion)
Clever workaround for dollar sign expansion issue

When to Use:

Text cleanup and standardization
Working around syntax limitations
Dynamic expression building

4.4 LEFT / RIGHT / MID

Purpose: Extracts portion of string from left, right, or middle

Syntax:

LEFT(Text, NumberOfChars)
RIGHT(Text, NumberOfChars)
MID(Text, StartPosition, NumberOfChars)

Project Example (from Transactions.txt, line 152):

UPPER(LEFT(MONTHNAME(DATE("Trade Date Full",'$(DateFormat)')),3)) AS "Trade Date Month Name Short",

Explanation:

Takes first 3 characters of month name: "January" → "Jan"
Converts to uppercase: "Jan" → "JAN"

When to Use:

Extracting year/month from date strings
Getting prefixes or suffixes
Parsing fixed-width data

4.5 CONCAT

Purpose: Concatenates values from multiple rows into single delimited string

Syntax:

CONCAT(FieldName, Delimiter)

Project Example (from PFC_at_Lodgement.txt, line 18-22):

[LastPFC]:
LOAD CONCAT(PORTFOLIO_CALCULATION_KEY & '_' & NUM([Valuation Date Full]) & '-' & NUM([Calculation Date Full]), 
            Chr(39) & ',' & Chr(39)) as PFCDList
RESIDENT mLastPFC;

LET vPFCDList = Chr(39) & peek('PFCDList') & Chr(39);

Explanation:

Creates comma-separated list: 'KEY1_DATE1-CALC1','KEY2_DATE2-CALC2'
Used later in MATCH() for filtering

When to Use:

Creating IN lists for filtering
Building dynamic WHERE clauses
Aggregating values into single field

4.6 COALESCE

Purpose: Returns first non-NULL value from list of fields

Syntax:

COALESCE(Value1, Value2, Value3, ...)

Project Example (from Transactions.txt, line 96):

COALESCE([Free Comment],'') AS "Free Comment",

Explanation:

If [Free Comment] is NULL → returns empty string ''
Prevents NULL values

When to Use:

Providing default values for NULLs
Merging fields where only one should have value

4.7 CHR

Purpose: Returns character corresponding to Unicode/ASCII value

Syntax:

CHR(Number)

Project Example (from PFC_at_Lodgement.txt, line 19):

LOAD CONCAT(..., Chr(39) & ',' & Chr(39)) as PFCDList

Explanation:

Chr(39) = single quote character (')
Used to build string with embedded quotes

Common CHR Codes:

Chr(9)  → Tab
Chr(10) → Line feed
Chr(13) → Carriage return
Chr(39) → Single quote
Chr(44) → Comma

When to Use:

Building strings with special characters
Creating delimited exports
Formatting text output

Section 5: Date & Time Functions

5.1 DATE

Purpose: Formats numeric date value as text date

Syntax:

DATE(NumericDate, FormatString)

Project Example (from Transactions.txt, line 135):

DATE("Trade Date Full",'$(DateFormat)') AS "Trade Date",

Format Codes:

YYYY → 4-digit year (2024)
MM   → 2-digit month (01-12)
DD   → 2-digit day (01-31)

When to Use:

Displaying dates in reports
Converting between date formats

5.2 YEAR / MONTH / DAY

Purpose: Extracts year, month, or day from date

Syntax:

YEAR(DateValue)
MONTH(DateValue)
DAY(DateValue)

Project Example (from Master_Calendar.txt):

Year(TempDate) AS Year, 
Month(TempDate) AS MonthName,
Day(TempDate) AS Day,

When to Use:

Creating fiscal period fields
Building calendar tables

5.3 WEEKDAY / WEEK

Purpose: Returns day of week or week number

Syntax:

WEEKDAY(DateValue)
WEEK(DateValue)

Project Example (from Transactions.txt):

IF(WEEKDAY("Trade Date Full") >= 4,
   DATE("Trade Date Full"+3-(WEEKDAY("Trade Date Full")-4),'$(DateFormat)'),
   DATE("Trade Date Full"+1,'$(DateFormat)')) AS "Trade Date TD+1BD",

Explanation:

Calculates next business day (skips weekends)

When to Use:

Identifying weekend vs weekday
Calculating business days

5.4 TODAY / NOW

Purpose: Returns current date or date-time

Syntax:

TODAY()
NOW()

Project Example (from Main.txt):

LET Var_Today = Today();

When to Use:

Filtering to current data
Calculating age/days elapsed

5.5 MONTHNAME

Purpose: Returns month and year as formatted text

Syntax:

MONTHNAME(DateValue)

Project Example (from Transactions.txt):

MONTHNAME(DATE("Trade Date Full",'$(DateFormat)'))

When to Use:

Creating month-year dimensions
Display formatting in charts

5.6 YEARSTART / YEAREND

Purpose: Returns first or last day of year

Syntax:

YEARSTART(DateValue)
YEAREND(DateValue)

When to Use:

Year-to-date analysis
Fiscal year calculations

5.7 MONTHSTART / MONTHEND

Purpose: Returns first or last day of month

Syntax:

MONTHSTART(DateValue)
MONTHEND(DateValue)

When to Use:

Month-to-date analysis
Monthly period filters

5.8 WEEKSTART / WEEKEND

Purpose: Returns first or last day of week

Syntax:

WEEKSTART(DateValue)
WEEKEND(DateValue)

When to Use:

Week-based analysis
Weekly aggregations

5.9 ADDYEARS / ADDMONTHS

Purpose: Adds specified number of years or months to date

Syntax:

ADDYEARS(DateValue, NumberOfYears)
ADDMONTHS(DateValue, NumberOfMonths)

When to Use:

Calculating anniversaries
Prior year comparisons

Section 6: Number Functions

6.1 NUM

Purpose: Formats number with specified format pattern

Syntax:

NUM(NumericValue, FormatPattern)

Project Example (from Capital_Info.txt):

NUM("Nominal Per Share",'#,##0.000000') AS "Nominal Per Share"

Format Patterns:

#,##0        → 1,234
#,##0.00     → 1,234.56
#,##0.000000 → 1,234.567890

When to Use:

Formatting numbers for display
Ensuring consistent decimal places

6.2 FLOOR / CEIL

Purpose: Rounds down (FLOOR) or up (CEIL) to nearest integer

Syntax:

FLOOR(NumericValue)
CEIL(NumericValue)

Project Example (from Dividend.txt):

Floor("Ex Dividend Date Full") AS ExDivDateNum,

Explanation:

Removes decimal part of date
Converts datetime to date-only integer

When to Use:

Removing time portion from datetime
Integer division

6.3 FABS (Absolute Value)

Purpose: Returns absolute value (removes negative sign)

Syntax:

FABS(NumericValue)

Project Example (from Sheet_05.txt):

FABS(ALT(Sum({$(xFILTER_SHEET05)} ...) / Sum({$(xFILTER_SHEET05)} [Nominal]),0))

When to Use:

Displaying amounts as positive
Variance analysis (ignoring direction)

Section 7: Conditional Functions

7.1 IF

Purpose: Returns one value if condition is true, another if false

Syntax:

IF(Condition, TrueValue, FalseValue)

Project Example (from Transactions.txt):

IF("Bus Trans Code"<>'BalBook' AND [Current Value Qc]>0, 'Sell',
    IF(("Bus Trans Code"<>'BalBook' AND [Current Value Qc]<0), 'Buy')
) AS "Buy or Sell",

When to Use:

Conditional logic
Data categorization
Handling special cases

7.2 ALT (Alternative)

Purpose: Returns first non-NULL value from list of expressions

Syntax:

ALT(Expression1, Expression2, Expression3, ...)

Project Example (from Transactions.txt):

IF(ALT([Dividend Reinvestment Amount Pc],0) = 0,
    [Payment Amount Pc],
    ALT([Interest Dividend Capital Event Pc],0) - ALT([Dividend Reinvestment Amount Pc],0)
) AS "Net Cash Received Pc",

When to Use:

NULL handling in calculations
Providing default values
Safe division

7.3 NULL / ISNULL

Purpose: Checks for NULL values

Syntax:

NULL()
ISNULL(Expression)

Project Example (from Mappings.txt):

MAPPING LOAD SECURITY_KEY, "Security Dividend Registrar" 
RESIDENT TmpSec 
WHERE NOT IsNull("Security Dividend Registrar");

When to Use:

Filtering out NULL values
NULL checks in IF statements

7.4 EMPTYISNULL

Purpose: Treats empty strings as NULL

Syntax:

EMPTYISNULL(Expression)

Project Example (from PFC_at_Lodgement.txt):

WHERE NOT ISNULL(EmptyIsNull([CA Record Date]))

When to Use:

Treating empty strings as missing values
Data quality checks

Section 8: Matching & Lookup Functions

8.1 APPLYMAP

Purpose: Looks up value from mapping table

Syntax:

APPLYMAP('MappingTableName', LookupKey, DefaultValue)

Project Example (from Transactions.txt):

APPLYMAP('mAccFramework',ACCOUNTING_FRAMEWORK_KEY,'-') AS "Accounting Framework Transaction",
APPLYMAP('mBroker',SECURITY_KEY,'-') AS "Broker",

Full Pattern:

// Create mapping
[mBroker]:
MAPPING LOAD SECURITY_KEY, BrokerName FROM Brokers.qvd (qvd);

// Use mapping
APPLYMAP('mBroker', SECURITY_KEY, 'Unknown') AS "Broker"

When to Use:

Looking up dimension values
Replacing codes with descriptions
Avoiding expensive JOINs

8.2 MATCH

Purpose: Checks if value exists in list (returns position or 0)

Syntax:

MATCH(Expression, Value1, Value2, Value3, ...)

Project Example (from Transactions.txt):

MATCH([Instrument Type], 'Equity','GDR/ADR','Right','Warrant', 'Covered warrant')

When to Use:

Checking if value is in a list
Multiple OR conditions
WHERE clause filtering

8.3 WILDMATCH

Purpose: Checks if value matches wildcard pattern

Syntax:

WILDMATCH(Expression, Pattern1, Pattern2, ...)

Wildcards:

* = any characters
? = single character

Project Example (from Transactions.txt):

IF(WILDMATCH([Bus Trans Code], '*Buy*'), 'Buy',
    IF(WILDMATCH([Bus Trans Code], '*Sell*'), 'Sell','-'))

When to Use:

Pattern matching with wildcards
Grouping similar values

Section 9: Aggregation Functions

9.1 SUM

Purpose: Adds up all values in an expression

Syntax:

SUM(Expression)
SUM({SetExpression} Expression)

Project Example (from Sheet_01.txt):

LET xSHEET01_Entitlement = REPLACE('Sum({$(xFILTER_SHEET01)} [Nominal Basis])','@','$');

When to Use:

Totaling amounts, quantities, values
With set analysis for filtered sums

9.2 COUNT

Purpose: Counts number of values (non-NULL)

Syntax:

COUNT(Expression)
COUNT(DISTINCT Expression)

Project Example (from Sheet_04.txt):

LET xSHEET04_BuyTrades = REPLACE('Count({...} DISTINCT [Bus Trans No])','@','$');

When to Use:

Counting records
Counting unique values

9.3 MAX / MIN

Purpose: Returns maximum or minimum value

Syntax:

MAX(Expression)
MIN(Expression)

Project Example (from Capital_Info.txt):

MAX("Share Date Full") AS "Share Date Full"

When to Use:

Finding latest/earliest dates
Finding highest/lowest values

9.4 AVG

Purpose: Calculates average (mean) value

Syntax:

AVG(Expression)

When to Use:

Calculating mean values
Statistical analysis

Section 10: Inter-Record Functions

10.1 PEEK

Purpose: Returns value from a previous row of loaded table

Syntax:

PEEK(FieldName)
PEEK(FieldName, RowNumber)
PEEK(FieldName, Offset, TableName)

Project Example (from PFC_at_Lodgement.txt):

LET vPFCDList = Chr(39) & peek('PFCDList') & Chr(39);

When to Use:

Getting previous row values
Retrieving values from temporary tables
Calculating differences from previous row

10.2 PREVIOUS

Purpose: Returns value from previous row (in chart expressions)

Note: Used in chart expressions, not load script

10.3 ROWNO / RECNO

Purpose: Returns current row number during load

Syntax:

ROWNO()  // Row number in result
RECNO()  // Row number in source

Project Example (from Transactions.txt):

LOAD 
    TDF AS [Trade Date Date Full], 
    RowNo() as RNo
RESIDENT TD
ORDER BY TDF;

When to Use:

Creating sequence numbers
Generating unique IDs

RECNO() → 1, 3, 7 (original row numbers) ROWNO() → 1, 2, 3 (result row numbers)


---

## 10.4 FIELDVALUE / FIELDVALUECOUNT

**Purpose:** Returns specific value from a field by position / counts distinct values

**Syntax:**
```qlik
FIELDVALUE(FieldName, RowNumber)     // Get Nth value (1-based)
FIELDVALUECOUNT(FieldName)           // Count distinct values

Project Example (from Master_Calendar.txt, line 41-43):

LOAD
    min(FieldValue('Trade Date', recno()))-1 AS MinDate,
    max(FieldValue('Trade Date', recno())) AS MaxDate
AUTOGENERATE FieldValueCount('Trade Date');

Explanation:

FIELDVALUECOUNT('Trade Date') : How many distinct trade dates exist?
AUTOGENERATE: Create that many rows
FIELDVALUE('Trade Date', recno()): Get Nth distinct trade date
Result: Iterates through all unique trade dates to find min/max

When to Use:

Iterating through distinct field values
Creating dynamic loops
Building calendar tables
Generating test data from existing values

Example:

// Get 5th distinct customer
FIELDVALUE('CustomerID', 5)

// Count distinct products
FIELDVALUECOUNT('ProductName')

// Loop through all distinct years
LOAD 
    FIELDVALUE('Year', IterNo()) as Year
WHILE IterNo() <= FIELDVALUECOUNT('Year');

// Create dimension from existing values
LOAD 
    FIELDVALUE('Status', ROWNO()) as Status
AUTOGENERATE FIELDVALUECOUNT('Status');

Important Notes:

FIELDVALUE is 1-based (first value = 1)
Returns values in internal sort order (not load order)
Works on already-loaded fields only
Useful for dynamic scripting

Section 11: Table Functions

11.1 NOOFROWS

Purpose: Returns number of rows in a table

Syntax:

NOOFROWS('TableName')

Note: Not directly found in project, but commonly used pattern

When to Use:

Checking if table has data
Loop control
Data quality checks
Dynamic table processing

Example:

// Check if table loaded successfully
IF NOOFROWS('Sales') > 0 THEN
    TRACE Table Sales loaded with $(NOOFROWS('Sales')) rows;
ELSE
    TRACE WARNING: Sales table is empty!;
END IF

// Store row count in variable
LET vRowCount = NOOFROWS('Transactions');

// Conditional processing
IF NOOFROWS('Errors') > 0 THEN
    STORE Errors INTO ErrorLog.qvd (qvd);
END IF

// Loop through all rows
FOR i = 0 TO NOOFROWS('TempTable')-1
    LET vValue = PEEK('FieldName', $(i), 'TempTable');
    TRACE Row $(i): $(vValue);
NEXT i;

11.2 QVDCREATETIME

Purpose: Returns timestamp when QVD file was created

Syntax:

QVDCREATETIME('QVDFilePath')

Use Case (for incremental load):

// Check if QVD exists and get last load time
LET vQVDPath = 'Data/Transactions.qvd';

IF NOT IsNull(QvdCreateTime('$(vQVDPath)')) THEN
    // QVD exists - do incremental load
    [TempLastLoad]:
    LOAD MAX([Trade Date]) AS vMaxDate 
    FROM [$(vQVDPath)] (qvd);
    
    LET vLastLoadDate = Peek('vMaxDate', 0, 'TempLastLoad');
    DROP TABLE TempLastLoad;
    
    TRACE Incremental load from $(vLastLoadDate);
    
    // Load only new records
    [Transactions]:
    LOAD * FROM Source.qvd (qvd)
    WHERE [Trade Date] > '$(vLastLoadDate)';
    
    // Append historical data
    CONCATENATE ([Transactions])
    LOAD * FROM [$(vQVDPath)] (qvd);
    
ELSE
    // QVD doesn't exist - full load
    TRACE Full load - QVD not found;
    [Transactions]:
    LOAD * FROM Source.qvd (qvd);
END IF

// Store updated QVD
STORE [Transactions] INTO [$(vQVDPath)] (qvd);

When to Use:

Incremental loading logic
Checking if QVD exists
Comparing file ages
Cache expiration logic

Example:

// Check QVD age
LET vFileAge = Now() - QvdCreateTime('Data.qvd');

IF vFileAge > 1 THEN
    TRACE QVD is more than 1 day old - refreshing;
    // Reload from source
ELSE
    TRACE Using cached QVD ($(vFileAge) days old);
    LOAD * FROM Data.qvd (qvd);
END IF

Section 12: Special Functions

12.1 DUAL

Purpose: Creates field with both numeric and text representation

Syntax:

DUAL(TextValue, NumericValue)

Project Example (from Master_Calendar.txt, line 11-12):

Dual(Month(TempDate) & ' - ' & Year(TempDate), 
     Year(TempDate) & Num(Month(TempDate),'00')) AS YearMonthName,

Dual(Week(weekstart(TempDate)) & '-' & WeekYear(TempDate), 
     Year(TempDate) & Num(Week(weekstart(TempDate)),'00')) as WeekYear,

Explanation:

Display value: "Jan - 2024" (text)
Sort value: "202401" (numeric)
Charts show text but sort by number
Solves problem: "Apr" sorts before "Jan" alphabetically

Visual Result:

Without DUAL:               With DUAL:
Month (sorted)              Month (sorted)
Apr 2024                    Jan 2024
Aug 2024                    Feb 2024
Dec 2024                    Mar 2024
Feb 2024                    Apr 2024
Jan 2024                    ...

When to Use:

Month names that need chronological sort
Week numbers with readable labels
Status codes with descriptions
Any field needing different display vs sort

Example:

// Month with proper sorting
DUAL('January', 1) as Month
DUAL('February', 2) as Month

// Status with readable text
DUAL('New', 1) as Status
DUAL('In Progress', 2) as Status
DUAL('Completed', 3) as Status
// Displays text but sorts numerically

// Priority levels
DUAL('Low', 1) as Priority
DUAL('Medium', 2) as Priority
DUAL('High', 3) as Priority
DUAL('Critical', 4) as Priority

// Quarter display
DUAL('Q1 2024', 20241) as Quarter
DUAL('Q2 2024', 20242) as Quarter

How DUAL Works:

QlikSense stores both text and number
Charts/tables display text
Sorting uses number
Aggregations can use number

Section 13: Set Analysis

13.1 Set Analysis Basics

Purpose: Filters data in expressions (aggregations) independent of current selections

Syntax:

SUM({SetExpression} Field)

Project Example (from General_Filters.txt, line 11-26):

LET xFILTER_Equity = '<[Instrument Type] *= {"Equity","GDR/ADR","Right","Warrant", "Covered warrant", "Bond"}>';
LET xFILTER_Brokerage = '<[Cost Type] = {"BROKER","COMMISSION"}>';
LET xFILTER_StampDuty = '(<[Cost Type] = {"*STMP"}> + <[Cost Class] = {"STAMP_DUTY"}>)';

Project Example (from Sheet_01.txt, line 59-63):

LET xSHEET01_Entitlement = REPLACE('Sum({$(xFILTER_SHEET01)} [Nominal Basis])','@','$');
LET xSHEET01_GrossAmountMyr = REPLACE('Sum({$(xFILTER_SHEET01)} [Interest Dividend Capital Event Pc])','@','$');

// Where xFILTER_SHEET01 is defined earlier as:
LET xFILTER_SHEET01 = '<[Bus Trans Code] *= {"DRP Dividend","Dividend", "CapRed"}
    , [Trade Date]={"<=@(=GetFieldSelections([Trade Date S1]))"}
    , [Payment Date Transaction]={">=@(=GetFieldSelections([Trade Date S1]))"}
> + <[Bus Trans Code] *= {"DRP Dividend","Dividend", "CapRed"}
    , [Trade Date]={"<=@(=GetFieldSelections([Trade Date S1]))"}
    , [PaymentClose] = {"0"}
>';

Explanation:

{...} : Set analysis modifier
<FieldName = {Value}> : Set modifier syntax
Multiple conditions separated by commas (AND logic)
+ combines sets (OR logic)

Basic Syntax Components:

SUM({<Field = {Value}>} Amount)
     │ │      │  │      │
     │ │      │  │      └─ Field to sum
     │ │      │  └─ Value to filter
     │ │      └─ Field to filter
     │ └─ Set modifier
     └─ Set expression brackets

13.2 Set Modifiers

Purpose: Applies filters within set analysis

Operators:

= : Equal to
+= : Add to current selection
-= : Exclude from current selection
*= : Keeps intersection with current selection
/= : Replaces current selection (symmetric difference)

Project Example (from General_Filters.txt, line 11-24):

// Exact match
LET xFILTER_Brokerage = '<[Cost Type] = {"BROKER","COMMISSION"}>';

// Wildcard match (contains "STMP")
LET xFILTER_StampDuty = '<[Cost Type] = {"*STMP"}>';

// Exclusion (NOT equal)
LET xFILTER_NotBrokerage = '<[Cost Type] -= {"BROKER","COMMISSION"}>';

// Exclusion of multiple
LET xFILTER_FEES_SHEET05 = '<[Cost Type] -= {"BROKER", "COMMISSION", "MY_CLRG", "*STMP", "BROKER_SST", "SCAN_SST"}, 
                             [Cost Class] -= {"STAMP_DUTY"}>';

// Wildcard match with *= operator
LET xFILTER_Equity = '<[Instrument Type] *= {"Equity","GDR/ADR","Right","Warrant"}>';

Explanation:

= : Include only these values
-= : Exclude these values (show everything else)
*= : Wildcard match ("*STMP" matches "ADD_STMP", "SELL_STMP", etc.)

Examples:

// Equal (exact match)
SUM({<Year = {2024}>} Sales)

// Multiple values (OR)
SUM({<Status = {'Approved', 'Completed'}>} Amount)

// Exclude values
SUM({<Status -= {'Cancelled', 'Rejected'}>} Amount)

// Wildcard match
SUM({<Product *= {'*Phone*'}>} Sales)
// Matches: 'iPhone', 'Android Phone', 'Phone Case'

// Multiple conditions (AND)
SUM({<Year = {2024}, Region = {'North'}>} Sales)

// Comparison operators
SUM({<Amount = {">1000"}>} Amount)
SUM({<Date = {">=01/01/2024"}>} Sales)

13.3 Dollar Sign Expansion

Purpose: Inserts variable values into expressions

Syntax:

$(VariableName)         // Expands variable value
$(=Expression)          // Evaluates expression

Project Example (from Sheet_01.txt, line 59):

LET xSHEET01_Entitlement = REPLACE('Sum({$(xFILTER_SHEET01)} [Nominal Basis])','@','$');
//                                          ^
//                          Variable expansion: $(xFILTER_SHEET01)

Explanation:

$(xFILTER_SHEET01) gets replaced with the filter definition
Result: Sum({<[Bus Trans Code] *= {"DRP Dividend",...}>} [Nominal Basis])

Project Pattern - Why REPLACE('@', '$')?

// PROBLEM: Dollar sign in string triggers immediate expansion
LET Var = 'Sum({$(Filter)} Amount)';  
// $(Filter) expands WHEN defining Var

// SOLUTION: Use @ as placeholder, replace with $ later
LET Var = REPLACE('Sum({@(Filter)} Amount)', '@', '$');
// @(Filter) stays literal, $ inserted later when Var is used

When Variable Expands:

// Definition time (SET - no expansion)
SET vYear = 2024;
SET vFormula = 'SUM(Sales)';

// Definition time (LET - evaluates)
LET vToday = Today();              // Stores numeric date value
LET vCount = COUNT(Customer);      // Error! Can't aggregate during script

// Usage time (in expression)
SUM({<Year = {$(vYear)}>} Sales)   // Expands to: Year = {2024}
$(vFormula)                        // Expands to: SUM(Sales)

Dynamic Expressions:

// Current vs Prior Year
SET vCurrentYear = '=Year(Today())';
SET vPriorYear = '=Year(Today())-1';

// In chart
SUM({<Year = {$(=vCurrentYear)}>} Sales)          // Current year sales
SUM({<Year = {$(=vPriorYear)}>} Sales)            // Prior year sales

// Conditional formatting
=$(=IF(SUM(Sales) > 1000000, 'Green', 'Red'))

// Complex variable
SET vYTDSales = 'SUM({<Date = {">=$(=YearStart(Today()))<=$(=Today())"}>} Sales)';
// Usage: $(vYTDSales)

Project Example (from Sheet_02.txt, line 32-33):

LET xSHEET02_Quantity = REPLACE('Sum({<@(xFILTER_SHEET02)>} [Nominal])','@','$');
//                                      ^
//                      @(var) will become $(var) after REPLACE

Advanced: Nested Expansion

LET vFilter = 'Year = {2024}';
LET vExpression = 'SUM({<$(vFilter)>} Sales)';

// When used: $(vExpression)
// Step 1: Expands to: SUM({<$(vFilter)>} Sales)
// Step 2: Expands to: SUM({<Year = {2024}>} Sales)

Section 14: Variable Operations

14.1 LET vs SET

Purpose: Define variables in script

Syntax:

SET VariableName = Expression;     // Stores as literal text
LET VariableName = Expression;     // Evaluates then stores

Project Example (from Main.txt, line 19):

LET Var_Today = Today();
// LET evaluates Today() → stores numeric value (e.g., 44941)

SET DateFormat = 'DD-MM-YYYY';
// SET stores literal text 'DD-MM-YYYY'

Project Example (from General_Filters.txt, line 11-30):

LET xFILTER_Equity = '<[Instrument Type] *= {"Equity","GDR/ADR","Right","Warrant"}>';
LET xFILTER_Brokerage = '<[Cost Type] = {"BROKER","COMMISSION"}>';
LET xFILTER_StampDuty = '(<[Cost Type] = {"*STMP"}> + <[Cost Class] = {"STAMP_DUTY"}>)';

All use LET to store text values (even though they look like formulas)

Project Example (from Variables.txt, line 3):

LET vSHEET05_ShowTransNo = 'Yes';
SET vNrUser = "1";

Key Differences:

// SET - stores literal text
SET vFormula = '=SUM(Sales)';
TRACE $(vFormula);  // Output: =SUM(Sales)

// LET - evaluates first
LET vFormula = '=SUM(Sales)';
TRACE $(vFormula);  // Output: =SUM(Sales) (same in script, different in UI)

// SET with calculation
SET vYear = Year(Today());
TRACE $(vYear);  // Output: Year(Today()) [literal text]

// LET with calculation
LET vYear = Year(Today());
TRACE $(vYear);  // Output: 2024 [evaluated]

// SET with equals sign
SET vExpression = '=SUM(Sales)/1000';
// In chart: $(vExpression) → Evaluates as expression

// LET with equals sign
LET vExpression = '=SUM(Sales)/1000';
// Same as SET in this case

When to Use Each:

// Use SET for:
SET DateFormat = 'DD-MM-YYYY';                    // Constants
SET vExpression = '=SUM(Sales)';                  // Chart expressions
SET vFilter = '<Year = {2024}>';                  // Set analysis filters

// Use LET for:
LET vToday = Today();                             // Evaluated values
LET vRowCount = NoOfRows('Table');                // Calculations
LET vMaxDate = Peek('MaxDate');                   // Function results
LET vPath = '$(AppPath)/Data/';                   // Concatenation with variables

Project Pattern:

// Define filter pattern with SET (literal text)
SET Filter = '<Field = {Value}>';

// Create specific measure with LET (can use variable expansion)
LET Measure = 'SUM({$(Filter)} Amount)';

// In chart expression:
=$(Measure)  // Expands to: SUM({<Field = {Value}>} Amount)

14.2 Variable Expansion

Purpose: Insert variable value into expression

Methods:

$(VarName) : Basic expansion
$(=Expression) : Evaluate expression

Project Example (throughout Sheet scripts):

// Define filter variable
LET xFILTER_SHEET01 = '<[Bus Trans Code] = {"Dividend"}>';

// Use in measure
LET xSHEET01_Entitlement = REPLACE('Sum({$(xFILTER_SHEET01)} [Nominal Basis])','@','$');
//                                          ^
//                              Variable expansion $(xFILTER_SHEET01)

Expansion Timing:

// Script time expansion (during load)
LET vPath = '$(AppPath)/Data/';
LOAD * FROM [$(vPath)File.qvd] (qvd);
// Expands immediately: [C:/MyApp/Data/File.qvd]

// Expression time expansion (in chart)
SET vSales = 'SUM(Sales)';
// In chart: $(vSales) → Expands when chart renders

// Evaluated expansion
$(=Today())           // Evaluates Today() function
$(=MAX(Year))         // Evaluates MAX(Year) from current data

Project Example (from Transactions.txt, line 189):

FROM [$(LIB_FOLDER)/$(DWHMODEL)/Transactions/Transactions.qvd] (qvd)
//     ^              ^
//     Variable       Variable
//     expansion      expansion

Example: Dynamic Date Range

// In script
LET vStartDate = Date(YearStart(Today()), 'YYYY-MM-DD');
LET vEndDate = Date(Today(), 'YYYY-MM-DD');

// In expression
SUM({<Date = {">=$(=vStartDate)<=$(=vEndDate)"}>} Sales)

// What it becomes:
// SUM({<Date = {">=2024-01-01<=2024-03-15"}>} Sales)

Example: Conditional Formula

// Define conditional measure
SET vMeasure = IF(GetSelectedCount(Year) = 1, 
                  'SUM(Sales)', 
                  'SUM(Sales) / COUNT(DISTINCT Year)');

// In chart
=$(vMeasure)
// If 1 year selected: SUM(Sales)
// If multiple years: SUM(Sales) / COUNT(DISTINCT Year)

Section 15: Table Maintenance

15.1 DROP TABLE

Purpose: Removes table from data model to free memory

Syntax:

DROP TABLE TableName;
DROP TABLES Table1, Table2, Table3;  // Multiple tables

Project Example (from Mappings.txt, line 19, 40, etc.):

// Load temp table
TmpBCat:
LOAD BUS_TRANS_CATEGORY_KEY, "Field1", "Field2", "Field3"
FROM [Source.qvd] (qvd);

// Create mappings
[mField1]: MAPPING LOAD KEY, "Field1" RESIDENT TmpBCat;
[mField2]: MAPPING LOAD KEY, "Field2" RESIDENT TmpBCat;

// Drop temp table to save memory
DROP TABLE TmpBCat;

Project Example (from PFC_at_Lodgement.txt, line 22):

DROP TABLES mLastPFC, LastPFC;

Project Example (from Transactions.txt, line 334):

Drop Tables TD2;

When to Use:

After creating mapping tables (don't need temp table anymore)
After extracting needed data
Removing intermediate calculation tables
Memory optimization

Example:

// Create dimension from fact
[Products]:
LOAD DISTINCT ProductID, ProductName
RESIDENT Sales;

// Don't need full sales anymore
DROP TABLE Sales;

// Multiple temporary tables
[Temp1]: LOAD * FROM Source1.qvd (qvd);
[Temp2]: LOAD * FROM Source2.qvd (qvd);
// ... process ...
DROP TABLES Temp1, Temp2;

Best Practice Pattern:

// 1. Load temporary data
[TempData]:
LOAD * FROM Source.qvd (qvd);

// 2. Extract what you need
[Final]:
LOAD Key, Value
RESIDENT TempData
WHERE Condition;

// 3. Drop temporary table
DROP TABLE TempData;

15.2 DROP FIELD

Purpose: Removes specific field(s) from table

Syntax:

DROP FIELD FieldName;
DROP FIELDS Field1, Field2 FROM TableName;

Project Example (from Master_Calendar.txt, line 53):

DROP FIELDS RangeStart, RangeEnd;

Removes temporary calculation fields no longer needed

When to Use:

Removing technical/temporary fields
Fields only needed for calculations
Reducing data model size
Hiding internal keys

Example:

// Create calculated field
LOAD 
    *,
    TempValue1 + TempValue2 as FinalValue
FROM Data.qvd (qvd);

// Drop intermediate fields
DROP FIELDS TempValue1, TempValue2;

// Drop from specific table
DROP FIELDS InternalKey, TechnicalFlag FROM Transactions;

// Drop system fields
DROP FIELDS %Key, %TableID;

15.3 STORE (QVD)

Purpose: Saves table to QVD file for future loads

Syntax:

STORE TableName INTO FilePath.qvd (qvd);

Project Example (from PFC_at_Lodgement.txt - implied but not shown):

// Would be used like:
STORE [PFC] INTO [$(QVD_PATH)/PFC_Latest.qvd] (qvd);
STORE [PFC_Holdings] INTO [$(QVD_PATH)/PFC_Holdings_Latest.qvd] (qvd);

Use Case: Incremental Loading

// Load new data
[Transactions]:
LOAD * FROM Source.qvd (qvd)
WHERE [Date] > '$(vLastLoadDate)';

// Append historical data
CONCATENATE ([Transactions])
LOAD * FROM Transactions_History.qvd (qvd)
WHERE [Date] <= '$(vLastLoadDate)';

// Store combined data
STORE [Transactions] INTO Transactions_History.qvd (qvd);

Use Case: Caching

// Check if cached QVD exists and is recent
IF NOT IsNull(QvdCreateTime('Cache.qvd')) THEN
    LET vAge = Now() - QvdCreateTime('Cache.qvd');
    IF vAge < 1 THEN
        // Use cache
        LOAD * FROM Cache.qvd (qvd);
        EXIT SCRIPT;
    END IF;
END IF;

// Calculate fresh data
[Data]:
LOAD * FROM [Complex calculation...];

// Store for next time
STORE [Data] INTO Cache.qvd (qvd);

When to Use:

Incremental loading
Caching expensive calculations
Data archiving
Breaking ETL into stages
Creating data snapshots

Example: Multi-Stage ETL

//=== Stage 1: Extract ===
[RawData]:
LOAD * FROM Database USING Connection;
STORE [RawData] INTO Stage1_Raw.qvd (qvd);

//=== Stage 2: Transform ===
[TransformedData]:
LOAD *, [Calculation] as NewField
FROM Stage1_Raw.qvd (qvd);
STORE [TransformedData] INTO Stage2_Transformed.qvd (qvd);

//=== Stage 3: Load ===
LOAD * FROM Stage2_Transformed.qvd (qvd);

QlikSense Functions & Techniques Glossary

📋 Table of Contents

Section 1: Data Loading Operations

1.1 LOAD Statement

1.2 MAPPING LOAD

1.3 RESIDENT

1.4 FROM (QVD Files)

1.5 INLINE Data

1.6 AUTOGENERATE

Section 2: Table Joining & Association

2.1 LEFT JOIN

2.2 RIGHT JOIN

2.3 INNER JOIN

2.4 CONCATENATE

2.5 LEFT KEEP

2.6 RIGHT KEEP

Section 3: Data Filtering & Control

3.1 WHERE Clause

3.2 GROUP BY

3.3 ORDER BY

3.4 DISTINCT

Section 4: String Functions

4.1 UPPER / LOWER

4.2 TEXT

4.3 REPLACE

4.4 LEFT / RIGHT / MID

4.5 CONCAT

4.6 COALESCE

4.7 CHR

Section 5: Date & Time Functions

5.1 DATE

5.2 YEAR / MONTH / DAY

5.3 WEEKDAY / WEEK

5.4 TODAY / NOW

5.5 MONTHNAME

5.6 YEARSTART / YEAREND

5.7 MONTHSTART / MONTHEND

5.8 WEEKSTART / WEEKEND

5.9 ADDYEARS / ADDMONTHS

Section 6: Number Functions

6.1 NUM

6.2 FLOOR / CEIL

6.3 FABS (Absolute Value)

Section 7: Conditional Functions

7.1 IF

7.2 ALT (Alternative)

7.3 NULL / ISNULL

7.4 EMPTYISNULL

Section 8: Matching & Lookup Functions

8.1 APPLYMAP

8.2 MATCH

8.3 WILDMATCH

Section 9: Aggregation Functions

9.1 SUM

9.2 COUNT

9.3 MAX / MIN

9.4 AVG

Section 10: Inter-Record Functions

10.1 PEEK

10.2 PREVIOUS

10.3 ROWNO / RECNO

Section 11: Table Functions

11.1 NOOFROWS

11.2 QVDCREATETIME

Section 12: Special Functions

12.1 DUAL

Section 13: Set Analysis

13.1 Set Analysis Basics

13.2 Set Modifiers

13.3 Dollar Sign Expansion

Section 14: Variable Operations

14.1 LET vs SET

14.2 Variable Expansion

Section 15: Table Maintenance

15.1 DROP TABLE

15.2 DROP FIELD

15.3 STORE (QVD)