Quick Summary

1. Current Scripts: 28 files (.txt format)
2. Total Reports: 24 reports across 14 sheets
3. Estimated Current Reload Time: 45-50 minutes
4. Optimization Potential: 50-70% reload time improvement

1. All 24 Reports Summary

Report Distribution by Sheet

Total: 14 sheets, 24 reports

2. All 28 Scripts Inventory

Actual Files (Confirmed via ls command)

Totals: 28 files, ~100 KB, ~4,000 lines

3. Data Architecture & Dependencies

Execution Order (Sequential - QlikSense Requirement)

┌─────────────────────────────────────────────────────────────┐
│ Phase 1: CONFIGURATION (Must run first)                     │
│   1. Main.txt                                               │
│   2. Variables.txt                                          │
└────────────────────────────┬────────────────────────────────┘
                             ↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 2: REFERENCE DATA                                     │
│   3. Mappings.txt (44 mappings)                            │
│   4. Master_Calendar.txt                                    │
└────────────────────────────┬────────────────────────────────┘
                             ↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 3: CORE FACTS ⚠️ CRITICAL BOTTLENECK                 │
│   5. Transactions.txt (must complete before next)           │
└────────────────────────────┬────────────────────────────────┘
                             ↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 4: ENRICHMENT (All depend on Transactions)            │
│   6. Capital_Info.txt                                       │
│   7. Corporate_Actions.txt                                  │
│   8. Dividend.txt                                           │
│   9. PFC_at_Lodgement.txt                                   │
└────────────────────────────┬────────────────────────────────┘
                             ↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 5: BUSINESS LOGIC                                     │
│   10. General_Filters.txt (20 variables)                    │
│   11. Trans_Codes.txt (DISABLED - skip)                     │
└────────────────────────────┬────────────────────────────────┘
                             ↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 6: AGGREGATIONS                                       │
│   12. Grouped_Total.txt                                     │
│   13. Monthly_Aggregations.txt                              │
└────────────────────────────┬────────────────────────────────┘
                             ↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 7: REPORT LAYERS (14 sheets)                          │
│   14. Sheet_01.txt  (Dividend Receivable)                   │
│   15. Sheet_02.txt  (Contract Notes)                        │
│   16. Sheet_03.txt  (Share Orders)                          │
│   17. Sheet_04.txt  (Purchase/Sales)                        │
│   18. Sheet_05.txt  (Transaction Details P&L) ⚠️ LARGEST   │
│   19. Sheet_6.txt   (Money Market)                          │
│   20. Sheet_07.txt  (Interest Earned)                       │
│   21. Sheet_09.txt  (Summary Transaction)                   │
│   22. Sheet_10.txt  (Dividend Received)                     │
│   23. Sheet_11.txt  (Daily Transaction Value)               │
│   24. Sheet_12.txt  (Transaction History)                   │
│   25. Sheet_13.txt  (Cost Adjustments)                      │
│   26. Sheet_14.txt  (Domestic Bonds)                        │
└────────────────────────────┬────────────────────────────────┘
                             ↓
┌─────────────────────────────────────────────────────────────┐
│ Phase 8: REPORT CONFIGURATION                               │
│   27. Report_Variables.txt                                  │
└─────────────────────────────────────────────────────────────┘

Optional (Independent):
│   28. Users.txt (can run anytime)

Critical Dependencies Verified

From Transactions.txt:

• Line 1-189: Uses APPLYMAP 49 times → Requires Mappings.txt first
• Line 20: Uses MAIN_DATE_KEY → Requires Master_Calendar.txt
• Line 190-237: LEFT KEEP operations → Must load base first

From Sheet Scripts:

• All use variables from General_Filters.txt (xFILTER_*)
• Sheet_01.txt, Sheet_10.txt: Use PFC table from PFC_at_Lodgement.txt
• Sheet_02.txt through Sheet_05.txt: Use IsS2/IsS3 flags from Transactions.txt

SECTION B: OPTIMIZATION OPPORTUNITIES

4. High Impact Optimizations

Optimization #1: Incremental Load for Transactions ⭐⭐⭐

Current State:

• Transactions.txt line 190: Full load every time
• WHERE clause: [Transaction Cancellation No] = 0 AND [Transaction Status Level No] >= 30
• Estimated time: 25-30 minutes

Proposed Change:

// Add at beginning of Transactions.txt
LET vQVDPath = '$(vAppPath)\QVD\Transactions_Incremental.qvd';

IF NOT IsNull(QvdCreateTime('$(vQVDPath)')) THEN
    [TempLastLoad]:
    LOAD MAX([Trade Date Full]) AS vMaxDate FROM [$(vQVDPath)] (qvd);
    LET vLastLoadDate = Peek('vMaxDate', 0, 'TempLastLoad');
    DROP TABLE TempLastLoad;
    LET vIncrementalLoad = 1;
ELSE
    LET vLastLoadDate = Date(AddYears(Today(), -2), 'YYYY-MM-DD');
    LET vIncrementalLoad = 0;
END IF

// Modify load to add date filter
FROM [$(LIB_FOLDER)/$(DWHMODEL)/Transactions/Transactions.qvd] (qvd)
WHERE [Trade Date Full] > '$(vLastLoadDate)'  // ⬅️ NEW LINE
  AND [Transaction Cancellation No] = 0
  AND [Transaction Status Level No] >= 30;

// After all transformations, CONCATENATE historical
IF vIncrementalLoad = 1 THEN
    CONCATENATE ([Transactions])
    LOAD * FROM [$(vQVDPath)] (qvd)
    WHERE [Trade Date Full] <= '$(vLastLoadDate)';
END IF

// Store updated QVD at end (before DROP TABLES)
STORE [Transactions] INTO [$(vQVDPath)] (qvd);

Expected Impact:

• Daily reload: 50 min → 8 min (84% faster)
• Weekly reload: 50 min → 15 min (67% faster)

Optimization #2: Consolidate Mapping Creation ⭐⭐⭐

Current State:

• Mappings.txt: 280 lines with repetitive patterns
• 44 mappings created with similar code repeated

Proposed Change: Create reusable subroutines at the beginning of Mappings.txt:

//=================================================================
// REUSABLE SUBROUTINES FOR MAPPING CREATION
//=================================================================

SUB LoadTempMappingTable(vTempName, vQVDPath, vFields)
    [$(vTempName)]:
    LOAD $(vFields)
    FROM [$(LIB_FOLDER)/$(DWHMODEL)/$(vQVDPath)] (qvd);
    TRACE >>> Loaded $(NoOfRows('$(vTempName)')) rows into $(vTempName);
END SUB

SUB CreateMappingFromTemp(vMapName, vTempTable, vKeyField, vValueField)
    [$(vMapName)]:
    MAPPING LOAD [$(vKeyField)], [$(vValueField)]
    RESIDENT [$(vTempTable)];
    TRACE >>> Created mapping: $(vMapName);
END SUB

SUB CreateDirectMapping(vMapName, vQVDPath, vKeyField, vValueField)
    [$(vMapName)]:
    MAPPING LOAD [$(vKeyField)], [$(vValueField)]
    FROM [$(LIB_FOLDER)/$(DWHMODEL)/$(vQVDPath)] (qvd);
    TRACE >>> Created direct mapping: $(vMapName);
END SUB

//=================================================================
// Now use subroutines for all mappings
//=================================================================

// Example: Business Transaction Category (previously 12 lines, now 6)
CALL LoadTempMappingTable('TmpBCat', 
    'Transactions/Business Transaction Category.qvd',
    'BUS_TRANS_CATEGORY_KEY, "Business Transaction Category Bus Trans Code", ...');
CALL CreateMappingFromTemp('mBusTransCode', 'TmpBCat', 
    'BUS_TRANS_CATEGORY_KEY', 'Business Transaction Category Bus Trans Code');
CALL CreateMappingFromTemp('mBusTransCodeName', 'TmpBCat',
    'BUS_TRANS_CATEGORY_KEY', 'Business Transaction Category Bus Trans Code Name');
// ... repeat for other mappings from same table
DROP TABLE TmpBCat;

// For single mappings (previously 4 lines, now 1)
CALL CreateDirectMapping('mElementalTransCodeName',
    'Transactions/Elementary Transaction.qvd',
    'ELEMENTARY_TRANS_KEY', 'Elementary Transaction Elementary Trans Code Name');

Expected Impact:

• Code reduction: 280 lines → ~150 lines (46% reduction)
• Easier to maintain and add new mappings
• Consistent error handling

Optimization #3: IsS5 Flag for Sheet 05 ⭐⭐⭐

Current State:

• Sheet_05.txt: 880 lines, 22 KB (largest script)
• Lines 111-375: Every measure uses 3-filter pattern (A+B+C)
• Example from line 111-114:

LET xSHEET05_NoOfShares = REPLACE('Sum({$(xFILTER_SHEET05A)} IF([Buy or Sell]=''Buy'', 1, -1) * [Nominal])
            + Sum({$(xFILTER_SHEET05B)} IF([Buy or Sell]=''Buy'', 1, -1) * [Nominal])
            + Sum({$(xFILTER_SHEET05C)} IF([Buy or Sell]=''Buy'', 1, -1) * [Nominal])
        ','@','$');

Root Cause (from Sheet_05.txt lines 23-42):

LET xFILTER_SHEET05A = '<[Bus Trans Category] = {"*Trades"}>';
LET xFILTER_SHEET05B = '<[Bus Trans Category] = {"Accounting"}, [Bus Trans SubCategory]={"Valuation", "Initialisation"}>';
LET xFILTER_SHEET05C = '<[Bus Trans Code] = {"CABuy", "CASell"}>';

Proposed Solution:

Step 1: Add IsS5 flag to Transactions.txt (after line 17, with IsS2 and IsS3):

[Transactions]:
LOAD *
  , ((MATCH([Instrument Type], 'Equity','GDR/ADR',...) ...) and MATCH([Buy or Sell], 'Buy','Sell')) AS IsS2
  , ((MATCH([Instrument Type], 'Equity',...) ...) OR (...)) AS IsS3
  
  // ⬇️ NEW FLAG FOR SHEET 05
  , IF(
      WILDMATCH([Bus Trans Category], '*Trades')
      OR ([Bus Trans Category]='Accounting' AND MATCH([Bus Trans SubCategory], 'Valuation', 'Initialisation'))
      OR MATCH([Bus Trans Code], 'CABuy', 'CASell'),
      1, 0
    ) AS IsS5
    
  , [Security Type] & '|' & Ifrs9Purpose & '|' & [Elementary Trans Code] AS Sheet5Rpt003Level
;

Step 2: Simplify Sheet_05.txt:

// Replace 3 filter definitions (lines 23-42) with single filter:
LET xFILTER_SHEET05 = '<IsS5={1}>';
LET xFILTER_SHEET05_SELL = '<IsS5={1}, [Buy or Sell] = {"Sell"}>';

// Now all measures become single-line (4 lines → 1 line):
LET xSHEET05_NoOfShares = 'Sum({$(xFILTER_SHEET05)} IF([Buy or Sell]=''Buy'', 1, -1) * [Nominal])';
LET xSHEET05_UnitPriceMyr = 'Sum({$(xFILTER_SHEET05)} [Current Value Pc]/[Nominal])';
// ... all 32 measures simplified

Expected Impact:

• Sheet_05.txt: 880 lines → ~320 lines (64% reduction)
• Each measure: 4 lines → 1 line
• Easier to maintain and modify

Optimization #4: PFC Caching ⭐⭐

Current State:

• PFC_at_Lodgement.txt: 128 lines, complex 7-step calculation
• Runs every reload (estimated 4 minutes)
• Lines 3-77: mLastPFC, vPFCDList, Tmp_DividendsAuxData, PFC, PFC_Holdings

Proposed Change: Add caching logic at beginning of PFC_at_Lodgement.txt:

LET vPFCQVD = '$(vAppPath)\QVD\PFC_Latest.qvd';
LET vPFCHoldingsQVD = '$(vAppPath)\QVD\PFC_Holdings_Latest.qvd';
LET vUseCachedPFC = 0;

IF NOT IsNull(QvdCreateTime('$(vPFCQVD)')) AND NOT IsNull(QvdCreateTime('$(vPFCHoldingsQVD)')) THEN
    LET vPFCAge = Now() - QvdCreateTime('$(vPFCQVD)');
    IF vPFCAge < 1 THEN  // Less than 1 day old
        [PFC]: LOAD * FROM [$(vPFCQVD)] (qvd);
        [PFC_Holdings]: LOAD * FROM [$(vPFCHoldingsQVD)] (qvd);
        LET vUseCachedPFC = 1;
        EXIT SCRIPT;  // Skip rest of script
    END IF
END IF

// Original calculation continues here if not cached...
// At end, add:
STORE [PFC] INTO [$(vPFCQVD)] (qvd);
STORE [PFC_Holdings] INTO [$(vPFCHoldingsQVD)] (qvd);

Expected Impact:

• First load: 4 min (unchanged)
• Cached load: 30 sec (87% faster)
• Cache hit rate: ~80% (PFC rarely changes daily)

Optimization #5: Simplify Previous Day Calculation ⭐⭐

Current State:

• Transactions.txt lines 255-305: 48 lines with 3 temp tables
• TmpPrevDay, MaxPrevDay, PrevDayTx

Proposed Simplification:

// Create BalBook reference with pre-calculated per-share values
[BalBookReference]:
LOAD 
    HOLDING_KEY,
    Num([Trade Date]) AS BB_Date,
    [Transaction Ik] AS BB_TransactionIk,
    [Current Value Myr] / [Nominal Basis] AS PrevValueMyr_PerShare,
    [Current Value Qc] / [Nominal Basis] AS PrevValueQc_PerShare,
    [Current Value Pc] / [Nominal Basis] AS PrevValuePc_PerShare,
    [Balance Cost Value Pc] / [Nominal Basis] AS PrevCostPc_PerShare,
    [Balance Cost Value Qc] / [Nominal Basis] AS PrevCostQc_PerShare
RESIDENT [Transactions] 
WHERE MATCH([Bus Trans Code], 'BalBook');

// Create mapping for previous day (composite key: HOLDING_KEY|DATE-1)
[MapPrevValues]:
MAPPING LOAD
    HOLDING_KEY & '|' & BB_Date AS PrevKey,
    BB_TransactionIk & '|' & 
    PrevValueMyr_PerShare & '|' & 
    PrevValueQc_PerShare & '|' & 
    PrevValuePc_PerShare & '|' & 
    PrevCostPc_PerShare & '|' & 
    PrevCostQc_PerShare AS PrevValues
RESIDENT BalBookReference;

DROP TABLE BalBookReference;

// Apply to Transactions
LEFT JOIN ([Transactions])
LOAD
    [Transaction Ik],
    ApplyMap('MapPrevValues', HOLDING_KEY & '|' & (Num([Trade Date]) - 1), NULL()) AS PrevValuesStr
RESIDENT [Transactions]
WHERE NOT MATCH([Bus Trans Code], 'BalBook');

// Parse concatenated values
LEFT JOIN ([Transactions])
LOAD
    [Transaction Ik],
    Num(SubField(PrevValuesStr, '|', 1)) AS [Previous Transaction Ik],
    Num(SubField(PrevValuesStr, '|', 2)) AS [Previous Value Myr],
    Num(SubField(PrevValuesStr, '|', 3)) AS [Previous Value Qc],
    Num(SubField(PrevValuesStr, '|', 4)) AS [Previous Value Pc],
    Num(SubField(PrevValuesStr, '|', 5)) AS [Previous Cost Value Pc],
    Num(SubField(PrevValuesStr, '|', 6)) AS [Previous Cost Value Qc]
RESIDENT [Transactions]
WHERE NOT IsNull(PrevValuesStr);

DROP FIELD PrevValuesStr FROM [Transactions];

Expected Impact:

• Code: 48 lines → ~35 lines (27% reduction)
• Temp tables: 3 → 1
• Execution time: ~3 min → ~2 min (33% faster)

5. Performance Benchmarks

Current State Baseline (Estimated)

Optimized State Projection

Daily Incremental Load: ~8-10 minutes (additional 60-70% savings)

SECTION C: MODULARIZATION

6. Proposed Folder Structure (Based on Current Scripts)

Current State: Flat Structure (28 Files)

Current Directory/
├── Main.txt
├── Variables.txt
├── Mappings.txt
├── Master_Calendar.txt
├── Transactions.txt
├── Capital_Info.txt
├── Corporate_Actions.txt
├── Dividend.txt
├── PFC_at_Lodgement.txt
├── General_Filters.txt
├── Trans_Codes.txt
├── Grouped_Total.txt
├── Monthly_Aggregations.txt
├── Sheet_01.txt
├── Sheet_02.txt
├── Sheet_03.txt
├── Sheet_04.txt
├── Sheet_05.txt
├── Sheet_6.txt
├── Sheet_07.txt
├── Sheet_09.txt
├── Sheet_10.txt
├── Sheet_11.txt
├── Sheet_12.txt
├── Sheet_13.txt
├── Sheet_14.txt
├── Report_Variables.txt
└── Users.txt

Proposed Structure: Organized by Function

QlikSense_Portfolio_ETL/
│
├── 📁 01_Configuration/               (2 files from current)
│   ├── Main.qvs                       ← from Main.txt
│   └── Variables.qvs                  ← from Variables.txt
│
├── 📁 02_Reference_Data/
│   │
│   ├── 📁 Mappings/                   (1 file, can be split later)
│   │   └── Mappings.qvs               ← from Mappings.txt
│   │
│   └── 📁 Dimensions/                 (1 file from current)
│       └── Master_Calendar.qvs        ← from Master_Calendar.txt
│
├── 📁 03_Core_Facts/                  (1 file from current)
│   └── Transactions.qvs               ← from Transactions.txt
│
├── 📁 04_Enrichment/                  (4 files from current)
│   ├── Capital_Info.qvs               ← from Capital_Info.txt
│   ├── Corporate_Actions.qvs          ← from Corporate_Actions.txt
│   ├── Dividend.qvs                   ← from Dividend.txt
│   └── PFC_at_Lodgement.qvs           ← from PFC_at_Lodgement.txt
│
├── 📁 05_Business_Logic/              (1 file from current)
│   └── General_Filters.qvs            ← from General_Filters.txt
│   (Note: Trans_Codes.txt excluded - already disabled)
│
├── 📁 06_Aggregations/                (2 files from current)
│   ├── Grouped_Total.qvs              ← from Grouped_Total.txt
│   └── Monthly_Aggregations.qvs       ← from Monthly_Aggregations.txt
│
├── 📁 07_Reports/
│   │
│   ├── 📁 Dividends/                  (2 files from current)
│   │   ├── Sheet_01_Dividend_Receivable.qvs     ← from Sheet_01.txt
│   │   └── Sheet_10_Dividend_Received.qvs       ← from Sheet_10.txt
│   │
│   ├── 📁 Equity_Trading/             (4 files from current)
│   │   ├── Sheet_02_Contract_Notes.qvs          ← from Sheet_02.txt
│   │   ├── Sheet_03_Share_Orders.qvs            ← from Sheet_03.txt
│   │   ├── Sheet_04_Purchase_Sales_Summary.qvs  ← from Sheet_04.txt
│   │   └── Sheet_05_Transaction_Details_PL.qvs  ← from Sheet_05.txt
│   │
│   └── 📁 Other_Reports/              (7 files from current)
│       ├── Sheet_06_Money_Market.qvs            ← from Sheet_6.txt
│       ├── Sheet_07_Interest_Earned.qvs         ← from Sheet_07.txt
│       ├── Sheet_09_Summary_Transaction.qvs     ← from Sheet_09.txt
│       ├── Sheet_11_Daily_Transaction_Value.qvs ← from Sheet_11.txt
│       ├── Sheet_12_Transaction_History.qvs     ← from Sheet_12.txt
│       ├── Sheet_13_Cost_Adjustments.qvs        ← from Sheet_13.txt
│       └── Sheet_14_Domestic_Bonds.qvs          ← from Sheet_14.txt
│
├── 📁 08_Report_Configuration/        (1 file from current)
│   └── Report_Variables.qvs           ← from Report_Variables.txt
│
├── 📁 09_Administration/              (1 file from current)
│   └── Users.qvs                      ← from Users.txt
│
├── 📁 QVD/                            (NEW - for optimizations)
│   ├── Transactions_Incremental.qvd   (created by Opt #1)
│   ├── PFC_Latest.qvd                 (created by Opt #4)
│   └── PFC_Holdings_Latest.qvd        (created by Opt #4)
│
├── 📁 Documentation/                  (NEW - recommended)
│   ├── README.md
│   ├── Data_Dictionary.xlsx
│   ├── Part_1_Business_Context.md
│   ├── Part_2_Dividend_Reports.md
│   ├── Part_3_Equity_Trading_Reports.md
│   ├── Part_4_Other_Reports.md
│   └── Part_5_Summary_Optimization.md
│
├── 📁 Backup/                         (NEW - for safety)
│   ├── Original_Scripts_20250130/
│   │   └── (all 28 original .txt files)
│   └── Trans_Codes_Archived.txt      (disabled script)
│
└── 📁 Logs/                           (NEW - for monitoring)
    └── Execution_Logs/

Total File Count:

• Current: 28 files (all .txt in root)
• Proposed: 28 files (all .qvs in organized folders)
• Note: Same files, just reorganized. No new files added (except folders for future use)

File Mapping Table

Master Script Include Order

Since QlikSense requires sequential execution, create a single include file:

//=================================================================
// FILE: MASTER_LOAD_ORDER.txt (or .qvs)
// Purpose: Define execution order for all scripts
//=================================================================

// Phase 1: Configuration
$(Include=01_Configuration/Main.qvs);
$(Include=01_Configuration/Variables.qvs);

// Phase 2: Reference Data
$(Include=02_Reference_Data/Mappings/Mappings.qvs);
$(Include=02_Reference_Data/Dimensions/Master_Calendar.qvs);

// Phase 3: Core Facts
$(Include=03_Core_Facts/Transactions.qvs);

// Phase 4: Enrichment
$(Include=04_Enrichment/Capital_Info.qvs);
$(Include=04_Enrichment/Corporate_Actions.qvs);
$(Include=04_Enrichment/Dividend.qvs);
$(Include=04_Enrichment/PFC_at_Lodgement.qvs);

// Phase 5: Business Logic
$(Include=05_Business_Logic/General_Filters.qvs);

// Phase 6: Aggregations
$(Include=06_Aggregations/Grouped_Total.qvs);
$(Include=06_Aggregations/Monthly_Aggregations.qvs);

// Phase 7: Reports - Dividends
$(Include=07_Reports/Dividends/Sheet_01_Dividend_Receivable.qvs);
$(Include=07_Reports/Dividends/Sheet_10_Dividend_Received.qvs);

// Phase 7: Reports - Equity Trading
$(Include=07_Reports/Equity_Trading/Sheet_02_Contract_Notes.qvs);
$(Include=07_Reports/Equity_Trading/Sheet_03_Share_Orders.qvs);
$(Include=07_Reports/Equity_Trading/Sheet_04_Purchase_Sales_Summary.qvs);
$(Include=07_Reports/Equity_Trading/Sheet_05_Transaction_Details_PL.qvs);

// Phase 7: Reports - Other
$(Include=07_Reports/Other_Reports/Sheet_06_Money_Market.qvs);
$(Include=07_Reports/Other_Reports/Sheet_07_Interest_Earned.qvs);
$(Include=07_Reports/Other_Reports/Sheet_09_Summary_Transaction.qvs);
$(Include=07_Reports/Other_Reports/Sheet_11_Daily_Transaction_Value.qvs);
$(Include=07_Reports/Other_Reports/Sheet_12_Transaction_History.qvs);
$(Include=07_Reports/Other_Reports/Sheet_13_Cost_Adjustments.qvs);
$(Include=07_Reports/Other_Reports/Sheet_14_Domestic_Bonds.qvs);

// Phase 8: Report Configuration
$(Include=08_Report_Configuration/Report_Variables.qvs);

// Phase 9: Administration (Optional)
// $(Include=09_Administration/Users.qvs);

7. Script Naming & Organization Standards

Naming Convention

Format: [Category]_[Description].qvs

Examples:

• Main.qvs (Configuration)
• Transactions.qvs (Core fact table)
• Sheet_01_Dividend_Receivable.qvs (Descriptive report name)

Variable Naming Standards

Migration Strategy:

// Keep old for backward compatibility (Phase 1)
LET xFILTER_Equity = '<[Instrument Type] *= {"Equity",...}>';  

// Add new alongside (Phase 2)
LET fltEquity = '<[Instrument Type] *= {"Equity",...}>';

// Remove old after full migration (Phase 3 - after 2-3 months)

File Header Template

//=================================================================
// QLIKSENSE ETL SCRIPT
//=================================================================
// Script Name: Transactions.qvs
// Original: Transactions.txt
// Module: 03_Core_Facts
// Version: 1.0.0
// Created: 2025-01-30
// Author: ETL Team
// Description: Main transaction fact table with IsS2/IsS3/IsS5 flags
//
// Dependencies:
//   - 02_Reference_Data/Mappings/Mappings.qvs (44 mappings)
//   - 02_Reference_Data/Dimensions/Master_Calendar.qvs
//
// Outputs:
//   - [Transactions] table (~7M rows)
//   - IsS2, IsS3, IsS5 flags
//
// Estimated Runtime:
//   - Full load: ~25 minutes
//   - Incremental: ~5 minutes (after Opt #1)
//
// Change Log:
//   2025-01-30 v1.0.0: Initial modularized version
//=================================================================

TRACE ========================================;
TRACE >>> Loading: Transactions;
TRACE >>> $(Now());
TRACE ========================================;

// Script content here...

TRACE ========================================;
TRACE >>> Completed: Transactions;
TRACE >>> Rows: $(NoOfRows('Transactions'));
TRACE ========================================;

8. Migration Roadmap

Phase 1: Preparation (Week 1)

Day 1: Full Backup

# Create dated backup folder
mkdir Backup/Original_Scripts_20250130

# Copy all current scripts
cp *.txt Backup/Original_Scripts_20250130/

# Verify backup
ls -lh Backup/Original_Scripts_20250130/

Day 2-3: Create Folder Structure

# Create all folders
mkdir -p 01_Configuration
mkdir -p 02_Reference_Data/Mappings
mkdir -p 02_Reference_Data/Dimensions
mkdir -p 03_Core_Facts
mkdir -p 04_Enrichment
mkdir -p 05_Business_Logic
mkdir -p 06_Aggregations
mkdir -p 07_Reports/Dividends
mkdir -p 07_Reports/Equity_Trading
mkdir -p 07_Reports/Other_Reports
mkdir -p 08_Report_Configuration
mkdir -p 09_Administration
mkdir -p QVD
mkdir -p Documentation
mkdir -p Logs

# Verify structure
tree -L 2

Day 4-5: Copy and Rename Files

# Configuration
cp Main.txt 01_Configuration/Main.qvs
cp Variables.txt 01_Configuration/Variables.qvs

# Reference Data
cp Mappings.txt 02_Reference_Data/Mappings/Mappings.qvs
cp Master_Calendar.txt 02_Reference_Data/Dimensions/Master_Calendar.qvs

# Core Facts
cp Transactions.txt 03_Core_Facts/Transactions.qvs

# Enrichment
cp Capital_Info.txt 04_Enrichment/Capital_Info.qvs
cp Corporate_Actions.txt 04_Enrichment/Corporate_Actions.qvs
cp Dividend.txt 04_Enrichment/Dividend.qvs
cp PFC_at_Lodgement.txt 04_Enrichment/PFC_at_Lodgement.qvs

# Business Logic
cp General_Filters.txt 05_Business_Logic/General_Filters.qvs
mv Trans_Codes.txt Backup/Trans_Codes_Archived.txt

# Aggregations
cp Grouped_Total.txt 06_Aggregations/Grouped_Total.qvs
cp Monthly_Aggregations.txt 06_Aggregations/Monthly_Aggregations.qvs

# Reports - Dividends
cp Sheet_01.txt 07_Reports/Dividends/Sheet_01_Dividend_Receivable.qvs
cp Sheet_10.txt 07_Reports/Dividends/Sheet_10_Dividend_Received.qvs

# Reports - Equity Trading
cp Sheet_02.txt 07_Reports/Equity_Trading/Sheet_02_Contract_Notes.qvs
cp Sheet_03.txt 07_Reports/Equity_Trading/Sheet_03_Share_Orders.qvs
cp Sheet_04.txt 07_Reports/Equity_Trading/Sheet_04_Purchase_Sales_Summary.qvs
cp Sheet_05.txt 07_Reports/Equity_Trading/Sheet_05_Transaction_Details_PL.qvs

# Reports - Other
cp Sheet_6.txt 07_Reports/Other_Reports/Sheet_06_Money_Market.qvs
cp Sheet_07.txt 07_Reports/Other_Reports/Sheet_07_Interest_Earned.qvs
cp Sheet_09.txt 07_Reports/Other_Reports/Sheet_09_Summary_Transaction.qvs
cp Sheet_11.txt 07_Reports/Other_Reports/Sheet_11_Daily_Transaction_Value.qvs
cp Sheet_12.txt 07_Reports/Other_Reports/Sheet_12_Transaction_History.qvs
cp Sheet_13.txt 07_Reports/Other_Reports/Sheet_13_Cost_Adjustments.qvs
cp Sheet_14.txt 07_Reports/Other_Reports/Sheet_14_Domestic_Bonds.qvs

# Report Configuration
cp Report_Variables.txt 08_Report_Configuration/Report_Variables.qvs

# Administration
cp Users.txt 09_Administration/Users.qvs

Phase 2: Testing (Week 2)

Test Modular Structure:

1. Create MASTER_LOAD_ORDER.qvs with all includes
2. Run full reload with modular structure
3. Compare row counts with original
4. Validate all 24 reports produce same results
5. Document any differences

Validation Checklist:

• Transactions row count matches
• All mappings loaded correctly
• Sheet auxiliary tables created
• All 24 reports produce data
• No execution errors in log

Phase 3: Optimization Implementation (Weeks 3-4)

Week 3: Implement Optimization #1 (Incremental Load)

• Modify 03_Core_Facts/Transactions.qvs
• Add incremental load logic
• Test with 1-day increment
• Validate row counts
• Monitor for 3 days

Week 4: Implement Optimizations #2-5

• Opt #2: Add subroutines to Mappings.qvs
• Opt #3: Add IsS5 to Transactions.qvs, simplify Sheet_05.qvs
• Opt #4: Add caching to PFC_at_Lodgement.qvs
• Opt #5: Simplify Previous Day in Transactions.qvs
• Test each optimization individually

Phase 4: Documentation & Cutover (Week 5)

Documentation:

• Complete README.md for each folder
• Update MASTER_LOAD_ORDER.qvs comments
• Create Data Dictionary
• Document all changes

Cutover:

• Final backup of old structure
• Switch to new modular structure
• Monitor for 1 week
• Address any issues
• Archive old .txt files

SECTION D: RECOMMENDATIONS

9. Data Quality Framework

Automated Data Quality Checks

Add to end of Transactions.qvs:

//=================================================================
// DATA QUALITY CHECKS
//=================================================================

[DQ_Checks]:
LOAD * INLINE [
CheckID, CheckName, Severity, Status, FailCount
];

// DQ-001: No NULL Transaction IK
LET vDQ001_Fail = Count(*) - Count([Transaction Ik]);
CONCATENATE ([DQ_Checks])
LOAD
    'DQ-001' AS CheckID,
    'No NULL Transaction IK' AS CheckName,
    'HIGH' AS Severity,
    IF($(vDQ001_Fail) = 0, 'PASS', 'FAIL') AS Status,
    $(vDQ001_Fail) AS FailCount
AUTOGENERATE 1;

// DQ-002: Buy/Sell Classification
[TempDQ002]:
LOAD Count(*) AS vDQ002_Fail
FROM Transactions
WHERE IsNull([Buy or Sell]) AND [Bus Trans Code] <> 'BalBook';

LET vDQ002_Fail = Peek('vDQ002_Fail', 0, 'TempDQ002');
DROP TABLE TempDQ002;

CONCATENATE ([DQ_Checks])
LOAD
    'DQ-002' AS CheckID,
    'Buy/Sell Classification' AS CheckName,
    'MEDIUM' AS Severity,
    IF($(vDQ002_Fail) = 0, 'PASS', 'FAIL') AS Status,
    $(vDQ002_Fail) AS FailCount
AUTOGENERATE 1;

// DQ-003: IsS2 Logic Validation
[TempDQ003]:
LOAD Count(*) AS vDQ003_Fail
FROM Transactions
WHERE IsS2 = 1 AND NOT (
    (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')
);

LET vDQ003_Fail = Peek('vDQ003_Fail', 0, 'TempDQ003');
DROP TABLE TempDQ003;

CONCATENATE ([DQ_Checks])
LOAD
    'DQ-003' AS CheckID,
    'IsS2 Flag Logic' AS CheckName,
    'HIGH' AS Severity,
    IF($(vDQ003_Fail) = 0, 'PASS', 'FAIL') AS Status,
    $(vDQ003_Fail) AS FailCount
AUTOGENERATE 1;

// DQ-004: Trade Date <= Settlement Date
[TempDQ004]:
LOAD Count(*) AS vDQ004_Fail
FROM Transactions
WHERE [Trade Date] > [Settlement Date];

LET vDQ004_Fail = Peek('vDQ004_Fail', 0, 'TempDQ004');
DROP TABLE TempDQ004;

CONCATENATE ([DQ_Checks])
LOAD
    'DQ-004' AS CheckID,
    'Trade Date <= Settlement Date' AS CheckName,
    'HIGH' AS Severity,
    IF($(vDQ004_Fail) = 0, 'PASS', 'FAIL') AS Status,
    $(vDQ004_Fail) AS FailCount
AUTOGENERATE 1;

// DQ-005: Row Count Range
LET vTxnCount = NoOfRows('Transactions');
CONCATENATE ([DQ_Checks])
LOAD
    'DQ-005' AS CheckID,
    'Row Count Range (5M-10M)' AS CheckName,
    'MEDIUM' AS Severity,
    IF($(vTxnCount) >= 5000000 AND $(vTxnCount) <= 10000000, 'PASS', 'FAIL') AS Status,
    $(vTxnCount) AS FailCount
AUTOGENERATE 1;

// Store results
STORE DQ_Checks INTO [Logs/DQ_Checks_$(Today()).qvd] (qvd);

// Count failures
LET vTotalFail = Count({<Status={'FAIL'}>} CheckID);
LET vHighFail = Count({<Status={'FAIL'}, Severity={'HIGH'}>} CheckID);

TRACE ========================================;
IF vHighFail > 0 THEN
    TRACE ⚠️ WARNING: $(vHighFail) HIGH severity DQ failures!;
END IF
IF vTotalFail > 0 THEN
    TRACE >>> Total DQ failures: $(vTotalFail);
ELSE
    TRACE >>> All DQ checks PASSED ✓;
END IF
TRACE ========================================;

10. Documentation Standards

README Template for Each Folder

Example: 07_Reports/Dividends/README.md

# Dividend Reports

## Overview
Contains 2 scripts for dividend-related reports (4 reports total).

## Scripts

### Sheet_01_Dividend_Receivable.qvs
- **Original:** Sheet_01.txt
- **Reports:** RPT_000019, RPT_000059
- **Purpose:** Dividend receivables (unpaid dividends)
- **Key Logic:** 
  - PaymentClose flag
  - PFC balance at lodgement (7-step)
  - Filters: Payment Date >= Report Date OR PaymentClose = 0
- **Auxiliary Tables:** 4 (S1AuxNprintingSecName, S1AuxNprintingPorGroup, etc.)
- **Measures:** 14
- **Dependencies:** 
  - Transactions.txt (base data)
  - PFC_at_Lodgement.txt (PFC balance)
  - Dividend.txt (voucher mapping)
- **Runtime:** ~30 seconds

### Sheet_10_Dividend_Received.qvs
- **Original:** Sheet_10.txt
- **Reports:** RPT_000040, RPT_000537
- **Purpose:** Dividend received (paid dividends)
- **Key Logic:**
  - GenCost conditional handling
  - Multiple IF branches for transaction types
  - Filters: PaymentClose = 1 (fully settled)
- **Auxiliary Tables:** 4 (S10AuxNprintingSecName, S10AuxNprintingPorGroup, etc.)
- **Measures:** 10
- **Dependencies:**
  - Transactions.txt (base data)
  - PFC_at_Lodgement.txt (PFC balance)
- **Runtime:** ~30 seconds

## Total Reports
- **2 scripts**
- **4 reports** (RPT_000019, RPT_000059, RPT_000040, RPT_000537)
- **~10.7 KB total code**

## Change Log
- 2025-01-30: Migrated from Sheet_01.txt and Sheet_10.txt

11. 8-Week Implementation Plan (Revised)

Overview

Total Duration: 8 weeks
Team Size: 2-3 developers
Risk Level: Medium (with proper backups and testing)

WEEK 1: PREPARATION & BACKUP

Objectives:

• Create comprehensive backups
• Set up folder structure
• Copy and rename all files

Tasks:

• Day 1: Full backup of current application and all scripts
• Day 2: Create new folder structure (9 folders)
• Day 3-4: Copy all 28 files to new folders with .qvs extension
• Day 5: Create MASTER_LOAD_ORDER.qvs and test includes

Deliverables:

• Backup folder with all 28 original .txt files
• New folder structure with 28 .qvs files
• MASTER_LOAD_ORDER.qvs working

Success Criteria:

• All files backed up
• All files copied and renamed correctly
• Test reload completes without errors

WEEK 2: TESTING & VALIDATION

Objectives:

• Validate modular structure produces same results
• Document any differences
• Fix any path issues

Tasks:

• Day 1: Full reload with modular structure
• Day 2: Compare row counts (Transactions, each sheet auxiliary table)
• Day 3: Validate all 24 reports produce data
• Day 4: Performance baseline measurement
• Day 5: Document validation results

Deliverables:

• Validation report comparing old vs new structure
• Performance baseline document
• Issue log (if any differences found)

Success Criteria:

• Row counts match exactly
• All reports produce data
• No critical errors

WEEK 3: OPTIMIZATION #1 - INCREMENTAL LOAD

Objectives:

• Implement incremental load for Transactions
• Test with small increments
• Validate accuracy

Tasks:

• Day 1: Add date check logic to Transactions.qvs
• Day 2: Add CONCATENATE historical data logic
• Day 3: Add STORE incremental QVD
• Day 4: Test with 1-day increment, validate row counts
• Day 5: Test with 1-week increment, monitor performance

Deliverables:

• Modified 03_Core_Facts/Transactions.qvs
• QVD/Transactions_Incremental.qvd created
• Performance comparison document

Success Criteria:

• Incremental load working correctly
• Row counts match full load
• Reload time reduced by 70-80%

Risk Mitigation:

• Keep full load option (comment out incremental for weekly full refresh)
• Monitor QVD file size
• Weekly full reload to prevent data drift

WEEK 4: OPTIMIZATION #2 & #3 - MAPPINGS & IsS5

Objectives:

• Add mapping subroutines
• Add IsS5 flag to Transactions
• Simplify Sheet_05

Tasks:

• Day 1-2: Add 3 subroutines to beginning of Mappings.qvs
• Day 2-3: Test all 44 mappings work correctly
• Day 4: Add IsS5 flag to Transactions.qvs (1 line after IsS3)
• Day 5: Refactor Sheet_05.qvs with single filter pattern

Deliverables:

• Modified 02_Reference_Data/Mappings/Mappings.qvs (with subroutines)
• Modified 03_Core_Facts/Transactions.qvs (with IsS5 flag)
• Modified 07_Reports/Equity_Trading/Sheet_05_Transaction_Details_PL.qvs

Success Criteria:

• All mappings load correctly
• Sheet 05 measures produce same results
• Sheet_05.qvs reduced from 880 to ~320 lines

WEEK 5: OPTIMIZATION #4 & #5 - PFC CACHING & PREVIOUS DAY

Objectives:

• Implement PFC caching
• Simplify Previous Day calculation

Tasks:

• Day 1-2: Add caching logic to PFC_at_Lodgement.qvs
• Day 2-3: Test cache hit/miss scenarios
• Day 4: Simplify Previous Day calculation in Transactions.qvs
• Day 5: Validate Previous Day values match original

Deliverables:

• Modified 04_Enrichment/PFC_at_Lodgement.qvs (with caching)
• Modified 03_Core_Facts/Transactions.qvs (simplified Previous Day)
• QVD/PFC_Latest.qvd and QVD/PFC_Holdings_Latest.qvd created

Success Criteria:

• PFC loads from cache when < 1 day old
• Previous Day values match original calculation
• Performance improved by 30%

WEEK 6: DATA QUALITY FRAMEWORK

Objectives:

• Implement automated data quality checks
• Create monitoring system

Tasks:

• Day 1-2: Add DQ checks to Transactions.qvs
• Day 3: Create DQ check results storage
• Day 4: Add execution logging framework
• Day 5: Test all DQ checks and logging

Deliverables:

• DQ checks added to Transactions.qvs
• Logs/DQ_Checks_YYYYMMDD.qvd created
• Execution logging framework implemented

Success Criteria:

• All 5 DQ checks running correctly
• DQ results stored to QVD
• Execution logs created

WEEK 7: DOCUMENTATION

Objectives:

• Complete all documentation
• Create README files
• Document changes

Tasks:

• Day 1: Create README.md for each folder (9 folders)
• Day 2: Create Data Dictionary (Excel)
• Day 3: Update master documentation
• Day 4: Create troubleshooting guide
• Day 5: Create training materials

Deliverables:

• README.md in each folder
• Data_Dictionary.xlsx
• Troubleshooting_Guide.md
• Training_Materials.pdf

Success Criteria:

• All folders documented
• Data dictionary complete
• Training materials ready

WEEK 8: FINAL TESTING & CUTOVER

Objectives:

• Final end-to-end testing
• Production cutover
• Post-cutover monitoring

Tasks:

• Day 1: Final end-to-end test with all optimizations
• Day 2: User acceptance testing
• Day 3: Final backup before cutover
• Day 4: Production cutover
• Day 5: Monitor and support

Deliverables:

• Final test results
• UAT sign-off
• Production cutover completed
• Post-cutover monitoring report

Success Criteria:

• All tests pass
• UAT approved
• Production running smoothly
• All 24 reports working

Implementation Checklist Summary

Week 1: ✓ Backup + Folder Structure
Week 2: ✓ Testing & Validation
Week 3: ⭐ Incremental Load (Opt #1)
Week 4: ⭐ Mappings + IsS5 (Opt #2 & #3)
Week 5: ⭐ PFC + Previous Day (Opt #4 & #5)
Week 6: 📊 Data Quality Framework
Week 7: 📚 Documentation
Week 8: 🚀 Final Testing & Cutover

Success Metrics

Risk Mitigation