QlikSense ETL Analysis

Overview: Dividend Reports Category

This section provides complete data lineage from raw QVD sources to final dividend reports, showing every transformation step.

Reports Covered:

1. RPT_000019 - Dividend Receivables (Sheet 01)
2. RPT_000059 - Dividend Receivable for Common Stock (Sheet 01)
3. RPT_000040 - Dividend Received Report (Sheet 10)
4. RPT_000537 - Dividend Track Record for Counter (Sheet 10)

🔄 Complete Data Flow Diagram

┌─────────────────────────────────────────────────────────────────────┐
│                        RAW DATA SOURCES (QVD)                        │
└─────────────────────────────────────────────────────────────────────┘
                                    ↓
        ┌───────────────────────────┼───────────────────────────┐
        ↓                           ↓                           ↓
┌───────────────┐        ┌──────────────────┐      ┌─────────────────────┐
│ Transactions  │        │ Corporate Action │      │  Dividends.qvd      │
│    .qvd       │        │ Transactions.qvd │      │                     │
└───────┬───────┘        └────────┬─────────┘      └──────────┬──────────┘
        │                         │                            │
        ↓                         ↓                            ↓
┌───────────────────────────────────────────────────────────────────────┐
│                    MAPPINGS PHASE (Mappings.txt)                      │
│  - mBusTransCode, mSecId, mSecName, mPor, mPorGrp, mBroker, etc.    │
└───────────────────────────────────┬───────────────────────────────────┘
                                    ↓
┌───────────────────────────────────────────────────────────────────────┐
│              CORE TRANSACTIONS TABLE (Transactions.txt)               │
│  - Base Load from Transactions.qvd                                   │
│  - LEFT JOIN Corporate_Actions.txt → CA Record Date                  │
│  - LEFT JOIN Open Payments → Outstanding Amount Pc/Qc                │
│  - Calculated: PaymentClose = IF(IsNull(Outstanding Amount), 1, 0)   │
│  - Calculated: Net Cash Received = Payment - Reinvestment - Outstanding│
│  - APPLYMAP dividend voucher from Dividend.txt                        │
└───────────────────────────────────┬───────────────────────────────────┘
                                    ↓
        ┌───────────────────────────┼───────────────────────────┐
        ↓                           ↓                           ↓
┌────────────────────┐   ┌──────────────────────┐   ┌──────────────────┐
│ PFC_at_Lodgement   │   │    Sheet_01.txt      │   │  Sheet_10.txt    │
│      .txt          │   │                      │   │                  │
│                    │   │  Auxiliary Tables:   │   │ Auxiliary Tables:│
│ Balance Nominal    │   │  - S1AuxSecName      │   │ - S10AuxSecName  │
│ Or Number At       │   │  - S1AuxPorGroup     │   │ - S10AuxPorGrp   │
│ Lodgement          │   │  - S1AuxQuotCur      │   │ - S10AuxDate     │
└─────────┬──────────┘   │  - S1AuxDate         │   │ - S10FreeComment │
          │              │                      │   │                  │
          │              │  Variables (14):     │   │ Variables (8):   │
          │              │  - xSHEET01_*        │   │ - xSHEET10_*     │
          │              └──────────────────────┘   └──────────────────┘
          │                         ↓                         ↓
          └─────────────────────────┼─────────────────────────┘
                                    ↓
┌───────────────────────────────────────────────────────────────────────┐
│                      FINAL REPORTS (NPrinting)                        │
│  RPT_000019 | RPT_000059 | RPT_000040 | RPT_000537                   │
└───────────────────────────────────────────────────────────────────────┘

📦 Phase 1: Raw Data Sources (QVD Files)

Source 1: Transactions.qvd

Location: [$(LIB_FOLDER)/$(DWHMODEL)/Transactions/Transactions.qvd]

Key Fields Used for Dividend Reports:

Filter Applied in Transactions.txt:

WHERE [Transaction Cancellation No] = 0        // Active transactions only
  AND [Transaction Status Level No] >= 30      // Confirmed and above

Source 2: Corporate Action Transactions.qvd

Location: [$(LIB_FOLDER)/$(DWHMODEL)/Corporate Action Transactions/Corporate Action Transactions.qvd]

Key Fields:

Loaded in: Corporate_Actions.txt (LEFT JOIN to Transactions)

Source 3: Corporate Action Notification.qvd

Location: [$(LIB_FOLDER)/$(DWHMODEL)/Corporate Action Transactions/Corporate Action Notification.qvd]

Key Fields:

Loaded in: Corporate_Actions.txt (LEFT JOIN to Transactions)

Source 4: Dividends.qvd

Location: [$(LIB_FOLDER)/$(DWHMODEL)/Dividends/Dividends.qvd]

Key Fields:

Loaded in: Dividend.txt (creates mapping mDividendVoucher)

Source 5: Transactions Open Payments.qvd

Location: [$(LIB_FOLDER)/$(DWHMODEL)/Transactions Open Payments/Transactions Open Payments.qvd]

Key Fields:

Loaded in: Transactions.txt (LEFT JOIN)

Key Business Rule:

// PaymentClose flag logic
PaymentClose = IF(IsNull([Outstanding Amount Pc]), 1, 0)

// PaymentClose = 1 → Payment is fully closed (used in RPT_000040, RPT_000537)
// PaymentClose = 0 → Payment is still open (used in RPT_000019, RPT_000059)

Source 6: Portfolio Calculation Results.qvd

Location: [$(LIB_FOLDER)/$(DWHMODEL)/Portfolio Calculation Results/Portfolio Calculation Results.qvd]

Key Fields:

Calculation Type Filter:

WHERE APPLYMAP('mPorCalc', PORTFOLIO_CALCULATION_KEY, NULL()) = 'DW_VAL_IFRS_PL_FB'
  AND "Calculation Result Type" = 1

Loaded in: PFC_at_Lodgement.txt

📦 Phase 2: Mapping Tables (Mappings.txt)

Mappings Created for Dividend Reports:

// 1. Business Transaction Code
[mBusTransCode]:
MAPPING LOAD BUS_TRANS_CATEGORY_KEY, "Business Transaction Category Bus Trans Code"
FROM Business Transaction Category.qvd;
// Maps to: "DRP Dividend", "Dividend", "CapRed", "GenCost", "DivPayIn", "Charge"

// 2. Security Name
[mSecName]:
MAPPING LOAD SECURITY_KEY, "Security Name"
FROM Security.qvd;

// 3. Portfolio Group
[mPorGrp]:
MAPPING LOAD PORTFOLIO_KEY, "Portfolio Group"
FROM Portfolio.qvd;

// 4. Portfolio Group Name
[mPorGrpName]:
MAPPING LOAD PORTFOLIO_KEY, "Portfolio Group Name"
FROM Portfolio.qvd;

// 5. Portfolio Calculation Type
[mPorCalc]:
MAPPING LOAD PORTFOLIO_CALCULATION_KEY, "Portfolio Calculation"
FROM Portfolio Calculation.qvd;
// Used to filter for 'DW_VAL_IFRS_PL_FB' calculation type

📦 Phase 3: Core Transactions Table (Transactions.txt)

Step 3.1: Base Load from Transactions.qvd

[Transactions]:
LOAD *,
    num([Trade Date]) as MAIN_DATE_KEY,
    
    // Calculate Net Cash Received
    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",
    
    IF(ALT([Dividend Reinvestment Amount Qc], 0) = 0,
       [Payment Amount Qc],
       ALT([Interest Dividend Capital Event Qc], 0) - 
       ALT([Dividend Reinvestment Amount Qc], 0)
    ) AS "Net Cash Received Qc",
    
    // DRP Flag
    IF([Dividend Reinvestment Amount Pc] = 0, 'N', 'Y') AS "DRP",
    
    // Holding-Date composite key
    "Holding Ik" & '|' & "Trade Date" AS HOLDING_DATE_KEY,
    
    // Sheet 10 specific field
    IF(MATCH([Bus Trans Code], 'DRP Dividend', 'Dividend'), 
       [Portfolio Group Name]) AS [Portfolio Group Name S10]
;

LOAD
    APPLYMAP('mBusTransCode', BUS_TRANS_CATEGORY_KEY, '-') AS "Bus Trans Code",
    APPLYMAP('mSecName', SECURITY_KEY, '-') AS "Security Name",
    APPLYMAP('mPorGrp', PORTFOLIO_KEY, '-') AS "Portfolio Group",
    APPLYMAP('mPorGrpName', PORTFOLIO_KEY, '-') AS "Portfolio Group Name",
    NUM("Dividend Per Share", '#,##0.0000') AS "Dividend Per Share",
    NUM("Nominal", '#,##0') AS "Nominal",
    NUM("Nominal Basis", '#,##0') AS "Nominal Basis",
    "Interest Dividend Capital Event Myr",
    "Interest Dividend Capital Event Pc",
    "Interest Dividend Capital Event Qc",
    "Dividend Reinvestment Amount Myr",
    "Dividend Reinvestment Amount Pc",
    "Dividend Reinvestment Amount Qc",
    "Payment Amount Myr",
    "Payment Amount Pc",
    "Payment Amount Qc",
    DATE("Payment Date Full", '$(DateFormat)') AS "Payment Date Transaction",
    DATE("Trade Date Full", '$(DateFormat)') AS "Trade Date",
    COALESCE([Free Comment], '') AS "Free Comment",
    NUM("Fx Rate Qc Pc", '#,##0.000000') AS "Fx Rate Qc Pc",
    HOLDING_KEY,
    "Holding Ik",
    SECURITY_KEY,
    PORTFOLIO_KEY,
    "Transaction Ik",
    [... other fields ...]
    
FROM [$(LIB_FOLDER)/$(DWHMODEL)/Transactions/Transactions.qvd] (qvd)
WHERE [Transaction Cancellation No] = 0        // Active only
  AND [Transaction Status Level No] >= 30;     // Confirmed+

Result: Base Transactions table with ~50+ fields

Step 3.2: Enrich with Dividend Voucher (Dividend.txt)

// Create mapping from Dividends.qvd
TempDividendsVoucher:
LOAD
    SECURITY_KEY,
    Floor("Ex Dividend Date Full") AS ExDivDateNum,
    "Dividend Voucher No"
FROM Dividends.qvd;

mDividendVoucher:
MAPPING LOAD
    SECURITY_KEY & '|' & ExDivDateNum AS DIVIDENDS_KEY,
    "Dividend Voucher No"
RESIDENT TempDividendsVoucher;

// Apply in Transactions.txt load
APPLYMAP('mDividendVoucher', 
         SECURITY_KEY & '|' & Floor("Trade Date Full"), 
         NULL()) AS "Voucher No"

Result: Transactions table now has Voucher No field

Step 3.3: Enrich with Corporate Action Data (Corporate_Actions.txt)

// First join - CA transaction details
LEFT JOIN ([Transactions]) 
LOAD DISTINCT
    TEXT("Bus Trans No") AS "Bus Trans No",
    'Yes' AS "Corporate Action Flag",
    "Event Sub Type",
    "Record Date" AS [CA Record Date],
    CORP_ACT_NOTIFICATION_KEY
FROM Corporate Action Transactions.qvd;

// Second join - CA notification details
LEFT JOIN ([Transactions]) 
LOAD DISTINCT
    CORP_ACT_NOTIFICATION_KEY,
    UPPER("Corporate Action Notification Dividend Type") AS "CA Dividend Type",
    UPPER("Corporate Action Notification Additional Text Additional Info") AS "CA Additional Info"
FROM Corporate Action Notification.qvd;

Result: Transactions table enriched with:

• Corporate Action Flag
• CA Record Date (critical for PFC matching)
• Event Sub Type
• CA Dividend Type
• CA Additional Info

Step 3.4: Add Outstanding Amounts (Transactions.txt)

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

// Create PaymentClose flag
LEFT KEEP (Transactions)
[IsPaymentClose]:
LOAD 
    "Transaction Ik",
    IF(IsNull("Outstanding Amount Pc"), 1, 0) AS PaymentClose
RESIDENT Transactions;

Key Business Logic:

PaymentClose = 1 → Payment fully closed (no outstanding)
PaymentClose = 0 → Payment still open (has outstanding amount)

Result: Transactions table now has:

• Outstanding Amount Pc
• Outstanding Amount Qc
• PaymentClose (flag)

Step 3.5: Calculate Balance at Lodgement (PFC_at_Lodgement.txt)

This is the most complex transformation for dividend reports.

// Step 1: Get latest Portfolio Calculation per day
[mLastPFC]:
LOAD
    PORTFOLIO_CALCULATION_KEY, 
    [Valuation Date Full], 
    MAX([Calculation Date Full]) AS [Calculation Date Full]
FROM Portfolio Calculation Results.qvd
GROUP BY PORTFOLIO_CALCULATION_KEY, [Valuation Date Full];

// Step 2: Create filter list (vPFCDList) of latest calculations
[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);

// Step 3: Build dividend key lookup from Dividends.qvd
[Tmp_DividendsAuxData]:
LOAD
    SECURITY_KEY & '|' & NUM(
        IF(ISNULL(EmptyIsNull([Record Date])), 
           [Ex Dividend Date Full], 
           [Record Date])
    ) AS DividendKey,
    [Ex Dividend Date Full]
FROM Dividends.qvd;

// Step 4: Add corporate action record dates
CONCATENATE
LOAD DISTINCT 
    SECURITY_KEY & '|' & NUM([CA Record Date]) AS DividendKey,
    [Trade Date] AS [Ex Dividend Date Full]
RESIDENT [Transactions]
WHERE NOT ISNULL(EmptyIsNull([CA Record Date]));

// Step 5: Create mapping to identify dividend dates
[mAuxDividend]:
MAPPING LOAD DividendKey, 1
RESIDENT [Tmp_DividendsAuxData];

// Step 6: Join PFC balance for dividend dates
JOIN (Tmp_DividendsAuxData)
LOAD 
    "Holding Ik" AS TmpPFCHoldingIk,
    "Balance Nominal Or Number" AS TmpPFCBalance,
    SECURITY_KEY & '|' & NUM([Valuation Date Full]) AS DividendKey
FROM Portfolio Calculation Results.qvd
WHERE APPLYMAP('mPorCalc', PORTFOLIO_CALCULATION_KEY, NULL()) = 'DW_VAL_IFRS_PL_FB'
  AND "Calculation Result Type" = 1
  AND ApplyMap('mAuxDividend', SECURITY_KEY & '|' & NUM([Valuation Date Full])) = 1
  AND MATCH(PORTFOLIO_CALCULATION_KEY & '_' & 
            NUM([Valuation Date Full]) & '-' & 
            NUM([Calculation Date Full]), 
            $(vPFCDList))
  AND [Valuation Date Full] < (Today(0) + 1);

// Step 7: Create final PFC table with composite key
[PFC]:
LEFT KEEP ([Transactions])
LOAD DISTINCT
    TmpPFCHoldingIk & '|' & DATE("Ex Dividend Date Full", '$(DateFormat)') AS HOLDING_DATE_KEY,
    "TmpPFCBalance" AS "Balance Nominal Or Number At Lodgement"
RESIDENT Tmp_DividendsAuxData;

Composite Key Logic:

HOLDING_DATE_KEY = "Holding Ik" & '|' & "Trade Date"

In Transactions: HOLDING_DATE_KEY = Holding Ik | Trade Date
In PFC:          HOLDING_DATE_KEY = Holding Ik | Ex Dividend Date

When they match → Balance at Lodgement is available

Result: PFC table with:

• HOLDING_DATE_KEY (join key to Transactions)
• Balance Nominal Or Number At Lodgement

Association: LEFT KEEP ensures only PFC records matching Transactions are retained

📦 Phase 4: Sheet-Specific Transformations

Sheet 01 - Dividend Receivable Reports (RPT_000019, RPT_000059)

Auxiliary Tables Created:

// 1. Security Name dimension for report filtering
S1AuxNprintingSecName:
LOAD DISTINCT 
    [Security Name], 
    [Security ID] AS [Security ID S1]
RESIDENT Transactions
WHERE MATCH([Bus Trans Code], 'DRP Dividend', 'Dividend', 'CapRed');

// 2. Portfolio Group dimension
S1AuxNprintingPorGroup:
LOAD DISTINCT 
    [Portfolio Group], 
    [Portfolio Group] AS [Portfolio Group S1]
RESIDENT Transactions
WHERE MATCH([Bus Trans Code], 'DRP Dividend', 'Dividend', 'CapRed');

// 3. Quotation Currency dimension
S1AuxNprintingQuotCur:
LOAD DISTINCT 
    [Quotation Currency], 
    [Quotation Currency] AS [Quotation Currency S1]
RESIDENT Transactions
WHERE MATCH([Bus Trans Code], 'DRP Dividend', 'Dividend', 'CapRed');

// 4. Date dimension - all dates in range with dividend transactions
[S1TmpAuxDate]:
LOAD 
    MIN([Trade Date]) AS MinTradeDate, 
    MAX([Trade Date]) AS MaxTradeDate
RESIDENT Transactions
WHERE MATCH([Bus Trans Code], 'DRP Dividend', 'Dividend', 'CapRed');

JOIN LOAD 
    Date AS [Tmp Trade Date S1], 
    MAIN_DATE_KEY AS TmpMAIN_DATE_KEY
RESIDENT [Main Calendar];

[S1AuxDate]:
LOAD [Tmp Trade Date S1] AS [Trade Date S1]
RESIDENT [S1TmpAuxDate]
WHERE TmpMAIN_DATE_KEY >= MinTradeDate 
  AND TmpMAIN_DATE_KEY <= MaxTradeDate;

DROP TABLE S1TmpAuxDate;

Purpose: These auxiliary tables enable NPrinting report filtering and user selections

Sheet 01 - Filter Definitions:

// Base filter for dividend transactions
LET xFILTER_SHEET01Date = 
    '<[Bus Trans Code] *= {"DRP Dividend", "Dividend", "CapRed"}>';

// Main filter for RECEIVABLE dividends (unpaid)
// Logic: (Trade Date <= Report Date AND Payment Date >= Report Date)
//     OR (Trade Date <= Report Date AND PaymentClose = 0)
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"}
    >';

Business Rule Explanation:

• Condition 1: Dividend traded on/before report date BUT payment date is after report date = Not yet paid
• Condition 2: Dividend traded on/before report date AND payment is not closed = Still outstanding

Sheet 01 - Measure Variables (14 Measures):

// 1. Entitlement (number of shares entitled)
LET xSHEET01_Entitlement = 
    'Sum({$(xFILTER_SHEET01)} [Nominal Basis])';

// 2. Gross Amount in MYR (before tax)
LET xSHEET01_GrossAmountMyr = 
    'Sum({$(xFILTER_SHEET01)} [Interest Dividend Capital Event Pc])';

// 3. Gross Amount in QC (before tax)
LET xSHEET01_GrossAmountQc = 
    'Sum({$(xFILTER_SHEET01)} [Interest Dividend Capital Event Qc])';

// 4. Tax in MYR (withheld)
// Calculation: Gross - Net = Tax
LET xSHEET01_TaxMyr = 
    'Sum({$(xFILTER_SHEET01)} 
         [Interest Dividend Capital Event Pc] - [Payment Amount Pc])';

// 5. Tax in QC (withheld)
LET xSHEET01_TaxQc = 
    'Sum({$(xFILTER_SHEET01)} 
         [Interest Dividend Capital Event Qc] - [Payment Amount Qc])';

// 6. Net Amount in MYR (after tax)
LET xSHEET01_NetAmountMyr = 
    'Sum({$(xFILTER_SHEET01)} [Payment Amount Pc])';

// 7. Net Amount in QC (after tax)
LET xSHEET01_NetAmountQc = 
    'Sum({$(xFILTER_SHEET01)} [Payment Amount Qc])';

// 8. Net Cash on Lodgement Date in QC
// This uses PFC balance at lodgement
LET xSHEET01_NetCashLodgementQc = 
    'Sum({$(xFILTER_SHEET01)} 
         [Balance Nominal Or Number At Lodgement] * [Dividend Per Share])';

// 9. Net Cash on Lodgement Date in MYR
LET xSHEET01_NetCashLodgementMyr = 
    'Sum({$(xFILTER_SHEET01)} 
         [Balance Nominal Or Number At Lodgement] * [Dividend Per Share] * [Fx Rate Qc Pc])';

// 10. Amount Reinvested in MYR (DRP)
LET xSHEET01_AmtReinvestedMyr = 
    'Sum({$(xFILTER_SHEET01)} [Dividend Reinvestment Amount Pc])';

// 11. Amount Reinvested in QC (DRP)
LET xSHEET01_AmtReinvestedQc = 
    'Sum({$(xFILTER_SHEET01)} [Dividend Reinvestment Amount Qc])';

// 12. Net Cash Received in MYR
// Calculation: Payment - Reinvestment - Outstanding
LET xSHEET01_NetCashReceivedMyr = 
    'Sum({$(xFILTER_SHEET01)} 
         [Payment Amount Pc] - 
         [Dividend Reinvestment Amount Pc] - 
         [Outstanding Amount Pc])';

// 13. Net Cash To Receive in MYR (still outstanding)
LET xSHEET01_NetCashToReceiveMyr = 
    'Sum({$(xFILTER_SHEET01)} [Outstanding Amount Pc])';

// 14. Net Cash Received in QC
LET xSHEET01_NetCashReceivedQc = 
    'Sum({$(xFILTER_SHEET01)} 
         [Payment Amount Qc] - 
         [Dividend Reinvestment Amount Qc] - 
         [Outstanding Amount Qc])';

// 15. Net Cash To Receive in QC
LET xSHEET01_NetCashToReceiveQc = 
    'Sum({$(xFILTER_SHEET01)} [Outstanding Amount Qc])';

// 16. Nominal (shares)
LET xSHEET01_Nominal = 
    'Sum({$(xFILTER_SHEET01)} [Nominal])';

Sheet 10 - Dividend Received Reports (RPT_000040, RPT_000537)

Auxiliary Tables Created:

// 1. Security Name dimension
S10AuxNprintingSecName:
LOAD DISTINCT 
    [Security Name], 
    [Security ID] AS [Security ID S10]
RESIDENT Transactions
WHERE MATCH([Bus Trans Code], 'DRP Dividend', 'Dividend', 'GenCost', 'DivPayIn', 'Charge');

// 2. Portfolio Group dimension
S10AuxNprintingPorGroup:
LOAD DISTINCT 
    [Portfolio Group], 
    [Portfolio Group] AS [Portfolio Group S10]
RESIDENT Transactions
WHERE MATCH([Bus Trans Code], 'DRP Dividend', 'Dividend', 'GenCost', 'DivPayIn', 'Charge');

// 3. Date dimension
S10AuxDate:
LOAD DISTINCT [Trade Date] AS [Trade Date S10]
RESIDENT Transactions
WHERE MATCH([Bus Trans Code], 'DRP Dividend', 'Dividend', 'GenCost', 'DivPayIn', 'Charge');

// 4. Free Comment for detail display
S10FreeComment:
LOAD "Transaction Ik", "Free Comment" AS "Free Comment S10"
RESIDENT Transactions
WHERE MATCH([Bus Trans Code], 'DRP Dividend', 'Dividend', 'GenCost', 'DivPayIn', 'Charge');

Key Difference from Sheet 01:

• Includes additional transaction types: GenCost, DivPayIn, Charge
• These represent general costs and other dividend-related charges

Sheet 10 - Filter Definition:

// Filter for RECEIVED dividends (fully paid)
LET xFILTER_SHEET10 = 
    '<[Bus Trans Code] = {"DRP Dividend", "Dividend", "GenCost", "DivPayIn", "Charge"}
        , [Trade Date] = P([Trade Date S10])
        , [PaymentClose] = {"1"}
    >';

Business Rule:

• PaymentClose = 1 → Payment is fully closed (no outstanding amount)
• P([Trade Date S10]) → Possible values from S10AuxDate selection

Sheet 10 - Measure Variables (8 Measures with Special Logic):

Special Logic for GenCost Transaction:

• GenCost transactions don't have gross dividend amounts
• For GenCost: Use Payment Amount as Gross (no tax)
• For others: Use Interest Dividend Capital Event as Gross

// 1. From Date (earliest trade date in selection)
LET xSHEET10_FromDate = 
    'Min({$(xFILTER_SHEET10)} [Trade Date S10])';

// 2. To Date (latest trade date in selection)
LET xSHEET10_ToDate = 
    'Max({$(xFILTER_SHEET10)} [Trade Date S10])';

// 3. Entitlement
LET xSHEET10_Entitlement = 
    'Sum({$(xFILTER_SHEET10)} [Nominal Basis])';

// 4. Dividend Per Share
LET xSHEET10_DivPerShare = 
    'Sum({$(xFILTER_SHEET10)} [Dividend Per Share])';

// 5. Gross Amount in MYR - WITH SPECIAL LOGIC
LET xSHEET10_GrossMyr = 
    'Sum({$(xFILTER_SHEET10)}
        IF([Bus Trans Code] = ''GenCost'', 
           [Payment Amount Pc],
           IF([Bus Trans Code] = ''DRP Dividend'', 
              [Interest Dividend Capital Event Qc],
              IF([Bus Trans Code] = ''DivPayIn'', 
                 [Interest Dividend Capital Event Qc],
                 IF([Bus Trans Code] = ''Charge'', 
                    [Interest Dividend Capital Event Qc],
                    IF([Bus Trans Code] = ''Dividend'', 
                       [Interest Dividend Capital Event Qc], 
                       0)))))
    )';

// 6. Gross Amount in QC - WITH SPECIAL LOGIC
LET xSHEET10_GrossQc = 
    'Sum({$(xFILTER_SHEET10)}
        IF([Bus Trans Code] = ''GenCost'', 
           [Payment Amount Qc],
           IF([Bus Trans Code] = ''DRP Dividend'', 
              [Interest Dividend Capital Event Qc],
              IF([Bus Trans Code] = ''DivPayIn'', 
                 [Interest Dividend Capital Event Qc],
                 IF([Bus Trans Code] = ''Charge'', 
                    [Interest Dividend Capital Event Qc],
                    IF([Bus Trans Code] = ''Dividend'', 
                       [Interest Dividend Capital Event Qc], 
                       0)))))
    )';

// 7. Tax in QC - WITH SPECIAL LOGIC (GenCost has no tax)
LET xSHEET10_TaxQc = 
    'Sum({$(xFILTER_SHEET10)}
        IF([Bus Trans Code] = ''GenCost'', 
           0,
           IF([Bus Trans Code] = ''DRP Dividend'', 
              [Interest Dividend Capital Event Qc] - [Payment Amount Qc],
              IF([Bus Trans Code] = ''DivPayIn'', 
                 [Interest Dividend Capital Event Qc] - [Payment Amount Qc],
                 IF([Bus Trans Code] = ''Charge'', 
                    [Interest Dividend Capital Event Qc] - [Payment Amount Qc],
                    IF([Bus Trans Code] = ''Dividend'', 
                       [Interest Dividend Capital Event Qc] - [Payment Amount Qc], 
                       0)))))
    )';

// 8. Tax in MYR - WITH SPECIAL LOGIC
LET xSHEET10_TaxMyr = 
    'Sum({$(xFILTER_SHEET10)}
        IF([Bus Trans Code] = ''GenCost'', 
           0,
           IF([Bus Trans Code] = ''DRP Dividend'', 
              [Interest Dividend Capital Event Pc] - [Payment Amount Pc],
              IF([Bus Trans Code] = ''DivPayIn'', 
                 [Interest Dividend Capital Event Pc] - [Payment Amount Pc],
                 IF([Bus Trans Code] = ''Charge'', 
                    [Interest Dividend Capital Event Pc] - [Payment Amount Pc],
                    IF([Bus Trans Code] = ''Dividend'', 
                       [Interest Dividend Capital Event Pc] - [Payment Amount Pc], 
                       0)))))
    )';

// 9. Net in QC
LET xSHEET10_NetQc = 
    'Sum({$(xFILTER_SHEET10)} [Payment Amount Qc])';

// 10. Net in MYR
LET xSHEET10_NetMyr = 
    'Sum({$(xFILTER_SHEET10)} [Payment Amount Pc])';

📦 Phase 5: Final Report Outputs

Report: RPT_000019 - Dividend Receivables

Purpose: Show all unpaid dividends as of a selected date

Tables Used:

1. Transactions (main fact table)
2. PFC (balance at lodgement)
3. S1AuxNprintingSecName (security filter)
4. S1AuxNprintingPorGroup (portfolio filter)
5. S1AuxNprintingQuotCur (currency filter)
6. S1AuxDate (date selection)

Key Dimensions:

• Security Name ([Security Name])
• Portfolio Group ([Portfolio Group])
• Trade Date ([Trade Date])
• Payment Date ([Payment Date Transaction])
• Quotation Currency ([Quotation Currency])

Key Measures: (from xSHEET01_* variables)

• Entitlement
• Gross Amount (MYR & QC)
• Tax (MYR & QC)
• Net Amount (MYR & QC)
• Outstanding Amount (MYR & QC)

Filter Applied:

Bus Trans Code IN ('DRP Dividend', 'Dividend', 'CapRed')
AND Trade Date <= Selected Date
AND (Payment Date >= Selected Date OR PaymentClose = 0)

Report: RPT_000059 - Dividend Receivable for Common Stock

Purpose: Show dividend receivables with comparison to holdings at lodgement

Tables Used:

1. Transactions (main fact table)
2. PFC (balance at lodgement) ← CRITICAL TABLE
3. Same auxiliary tables as RPT_000019

Key Dimensions:

• Security Name
• Portfolio Group
• Ex-Dividend Date (via PFC join)

Key Measures:

• Net Cash on Lodgement Date (QC) = Balance At Lodgement × Dividend Per Share
• Net Cash on Lodgement Date (MYR) = Balance At Lodgement × Dividend Per Share × FX Rate
• Amount Reinvested
• Net Cash Received
• Net Cash To Receive

Business Logic:

IF holdings at lodgement > current nominal THEN
    Possible dilution or share changes
ELSE
    Expected dividend based on lodgement holdings

Report: RPT_000040 - Dividend Received Report

Purpose: Show all fully paid dividends in a date range

Tables Used:

1. Transactions (main fact table)
2. S10AuxNprintingSecName (security filter)
3. S10AuxNprintingPorGroup (portfolio filter)
4. S10AuxDate (date selection)
5. S10FreeComment (transaction comments)

Key Dimensions:

• Security Name
• Portfolio Group
• Trade Date
• Free Comment

Key Measures: (from xSHEET10_* variables)

• From Date / To Date (date range)
• Entitlement
• Dividend Per Share
• Gross Amount (with GenCost logic)
• Tax (with GenCost logic)
• Net Amount

Filter Applied:

Bus Trans Code IN ('DRP Dividend', 'Dividend', 'GenCost', 'DivPayIn', 'Charge')
AND PaymentClose = 1
AND Trade Date IN Selected Dates

Report: RPT_000537 - Dividend Track Record for Counter

Purpose: Historical dividend tracking by security (counter) with payment details

Tables Used:

• Same as RPT_000040

Key Dimensions:

• Security Name (primary grouping)
• Trade Date (chronological order)
• Portfolio Group

Key Measures:

• Cumulative metrics over date range
• Payment history status
• Free Comment for transaction details

Special Features:

• Time-series view of dividend payments
• Grouping by security counter
• Date range selection via S10AuxDate

📊 Complete Field Mapping Summary

Source → Transactions → Reports Field Flow:

🔍 Critical Transformations Recap

1. PaymentClose Flag Calculation:

PaymentClose = IF(IsNull(Outstanding Amount Pc), 1, 0)

Logic:
- No outstanding amount → PaymentClose = 1 → PAID (Sheet 10 reports)
- Has outstanding amount → PaymentClose = 0 → UNPAID (Sheet 01 reports)

2. Net Cash Received Calculation:

Net Cash Received Pc = 
    IF(Dividend Reinvestment Amount Pc = 0,
       Payment Amount Pc,
       Interest Dividend Capital Event Pc - 
       Dividend Reinvestment Amount Pc - 
       Outstanding Amount Pc)

Logic:
- Full cash dividend: Use Payment Amount directly
- DRP (reinvestment): Gross - Reinvestment - Outstanding = Net Cash

3. Balance at Lodgement Join:

Join Key: HOLDING_DATE_KEY = "Holding Ik" & '|' & DATE

Transactions side: Holding Ik | Trade Date
PFC side:          Holding Ik | Ex Dividend Date

When matched: Balance Nominal Or Number At Lodgement is available
Used to calculate: Expected dividend = Balance × Dividend Per Share

4. GenCost Transaction Logic (Sheet 10):

IF Bus Trans Code = 'GenCost' THEN
    Gross = Payment Amount (no separate gross field)
    Tax = 0 (GenCost has no tax withholding)
ELSE
    Gross = Interest Dividend Capital Event
    Tax = Gross - Payment Amount

📋 Data Quality Checks

Critical Validations in the ETL:

1. Transaction Status Filter:

   WHERE [Transaction Cancellation No] = 0        // Only active
     AND [Transaction Status Level No] >= 30      // Confirmed and above
   

2. PFC Latest Calculation:

   // Ensures only the last calculation per day is used
   MAX([Calculation Date Full]) GROUP BY PORTFOLIO_CALCULATION_KEY, [Valuation Date Full]
   

3. Date Boundary Check:

   AND [Valuation Date Full] < (Today(0) + 1)     // No future dates
   

4. Null Handling:

   COALESCE([Free Comment], '')                    // Replace null with empty
   ALT([Dividend Reinvestment Amount Pc], 0)       // Default to 0
   

🎯 Key Performance Indicators (KPIs) by Report

RPT_000019 - Dividend Receivables:

RPT_000059 - Dividend Receivable for Common Stock:

RPT_000040 - Dividend Received:

RPT_000537 - Dividend Track Record:

🔧 Troubleshooting Guide

Issue 1: Missing Balance at Lodgement

Symptom: Balance Nominal Or Number At Lodgement is null

Possible Causes:

1. No PFC calculation available for that date
2. Holding Ik mismatch between Transactions and PFC
3. Ex-dividend date doesn't match Trade Date or CA Record Date

Solution:

// Check if PFC exists for the date
SELECT COUNT(*) 
FROM PFC
WHERE HOLDING_DATE_KEY = [Holding Ik] & '|' & [Trade Date]

Issue 2: Incorrect PaymentClose Flag

Symptom: Dividends showing in wrong report (Sheet 01 vs Sheet 10)

Possible Causes:

1. Outstanding Amount not loaded from Open Payments QVD
2. Outstanding Amount Pc is 0 but not NULL

Solution:

// Verify PaymentClose logic
SELECT Transaction Ik, Outstanding Amount Pc, PaymentClose
FROM Transactions
WHERE Bus Trans Code IN ('Dividend', 'DRP Dividend')

Issue 3: Duplicate Dividend Records

Symptom: Same dividend appearing multiple times

Possible Causes:

1. Multiple corporate action records for same transaction
2. Multiple PFC calculations joined

Solution:

// Ensure DISTINCT in loads
LOAD DISTINCT ...
FROM Corporate Action Transactions.qvd

// Verify latest PFC filter
WHERE MATCH(..., $(vPFCDList))

📚 Business Rules Reference

Transaction Type Classification:

Payment Status Logic:

Receivable (Sheet 01):
- Trade Date <= Report Date
- AND (Payment Date >= Report Date OR PaymentClose = 0)
→ Dividend is declared but not yet paid

Received (Sheet 10):
- PaymentClose = 1
→ Dividend is fully paid (no outstanding)

Currency Hierarchy:

Quotation Currency (QC) → FX Rate → Portfolio Currency (PC/MYR)

Example:
- Security quoted in USD (QC)
- Portfolio in MYR (PC)
- Dividend Per Share: $0.50
- FX Rate: 4.20
- Dividend in MYR: $0.50 × 4.20 = RM 2.10

✅ Validation Checklist

Before using dividend reports, verify:

• Latest PFC calculations are loaded (vPFCDList)
• Open Payments are up to date
• Corporate Action records are synchronized
• Dividend voucher mapping is complete
• Date ranges in auxiliary tables cover report period
• FX rates are current
• Transaction statuses are >= 30 (Confirmed)
• No cancelled transactions included (Cancellation No = 0)