QlikView developer on retainer: CVSD_EnrollmentAnalytics QVD CONCATENATE GradeLevel zero-padded ‘01’ meets Infinite Campus integer 1 — 21 listbox values instead of 12 — K–9 enrollment trends collapse to zero for FY2025–2026

QlikView 12.00 SR12 • Windows Server 2016 • CVSD_EnrollmentAnalytics.qvw • QVD incremental load • CONCATENATE • dual values • TEXT(NUM#()) type coercion • Set Analysis • retainer billing

The Cascade Valley School District’s enrollment analytics application had been running correctly for seven years when a SIS migration quietly broke every grade-level trend chart in the district’s QlikView dashboard. The reload script ran without errors. The QVW opened without warnings. The data looked plausible at a glance — grades were present, enrollment counts were present. But any chart that used a grade-level filter or dimension showed current-year enrollment as near-zero for every grade from kindergarten through ninth. When the district’s Analytics Coordinator pulled the FY2025–2026 enrollment figures in October 2026 to prepare the annual Title I funding application, she found that the dashboard showed an apparent 91% decline in K–9 enrollment compared to FY2024–2025. The QlikView developer retainer was engaged the same afternoon.

Cascade Valley ran QlikView 12.00 SR12 on Windows Server 2016. The primary analytics application, CVSD_EnrollmentAnalytics.qvw, used a standard QVD incremental load pattern: a QVD layer stored seven years of historical enrollment records (Enrollment_Historical.qvd, 847,293 records covering SY2018–19 through SY2024–25), and a SQL incremental load appended the current school year’s records from the live Student Information System database each time the QVW reloaded. The reload ran nightly at 01:30 AM via QlikView Publisher, and had done so reliably since 2019. The application’s central data model consisted of a single ENROLLMENT_FACT table built from those two sources via QlikView’s CONCATENATE load, linked to calendar and student dimension tables. The GradeLevel field was the primary dimension for all enrollment trend analyses.

In May 2026, the district migrated its Student Information System from the legacy platform to Infinite Campus. The migration was IT’s project — the analytics team and the QlikView developer were not notified of the schema changes in the new SIS. In the legacy SIS, the GradeLevel field was stored and exported as a zero-padded CHAR(2): '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12'. Every historical enrollment record in the QVD was stored with this zero-padded format. Infinite Campus returns GradeLevel as an INTEGER column from the SQL Server backing store: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12. Beginning with the first nightly reload after the May 2026 migration, the SQL incremental load pulled current-year students with integer-typed GradeLevel values.

How QlikView CONCATENATE handles type mismatches — and what goes wrong

In QlikView, every field value is stored internally as a dual value: a pair consisting of a numeric component and a string component. When QlikView loads an integer 1 from a SQL INTEGER column, it stores the dual value (numeric=1, text='1'). When QlikView loads the string '01' from a CHAR(2) column, it attempts to parse the string as a number — '01' parses to 1 numerically — and stores the dual value (numeric=1, text='01'). Both values represent the concept of “first grade,” and their numeric components are identical. But their string components differ: '1' versus '01'.

QlikView considers two dual values identical only when both the numeric and string components match. Because (numeric=1, text='1') and (numeric=1, text='01') have different string components, they are treated as two distinct values in QlikView’s internal value table. When the QVD load produces '01' through '12' and the SQL load produces integers 1 through 12, the CONCATENATE(ENROLLMENT_FACT) statement merges both sets of records into a single ENROLLMENT_FACT table, but the GradeLevel field now contains both sets of values simultaneously:

-- CVSD_EnrollmentAnalytics.qvw reload script (excerpt)
-- Step 1: Load historical enrollment from QVD
ENROLLMENT_FACT:
LOAD
    EnrollmentID,
    StudentID,
    GradeLevel,        // QVD stored type: CHAR(2) — values '01','02',...,'12'
    SchoolCode,
    EnrollmentDate,
    ExitDate,
    SchoolYear
FROM [$(vQVDPath)\Enrollment_Historical.qvd] (QVD);

// ENROLLMENT_FACT has 847,293 rows after QVD load.
// GradeLevel distinct values: '01','02','03','04','05','06','07','08','09','10','11','12' (12 values)

-- Step 2: CONCATENATE with SQL incremental load (SY2025-2026)
CONCATENATE(ENROLLMENT_FACT)
SQL SELECT
    EnrollmentID,
    StudentID,
    GradeLevel,        -- Infinite Campus SQL type: INTEGER — values 1,2,...,12
    SchoolCode,
    EnrollmentDate,
    ExitDate,
    SchoolYear
FROM dbo.vw_EnrollmentActive
WHERE SchoolYear = $(vCurrentYear);

// ENROLLMENT_FACT now has 894,586 rows (847,293 historical + 47,293 current-year).
// GradeLevel DISTINCT values in QlikView list box: 21
// '01','02','03','04','05','06','07','08','09','10','11','12' (from QVD: 12 values)
// '1','2','3','4','5','6','7','8','9'                        (from SQL: 9 additional values)
// Note: '10','11','12' have identical text representation in both sources — they match.
// But '01' ≠ '1', '02' ≠ '2', ..., '09' ≠ '9' — 9 grade-level pairs split into separate values.

The reload succeeded with no errors or warnings from QlikView Publisher. The QVW opened and displayed enrollment figures for all grades. But the GradeLevel list box showed 21 distinct values instead of the expected 12 — and any Set Analysis expression or dimension filter that referenced the zero-padded format ({'01'}, {'02'}, etc.) now silently excluded all current-year students. The 14 grade-level trend charts in the dashboard all used expressions of the form COUNT({<GradeLevel={'01'}>} DISTINCT StudentID), which was the correct format for the historical data. From May 2026 onward, each of these charts showed historical enrollment for the zero-padded values and zero current-year enrollment for grades 1 through 9.

Why the problem took five months to surface

The enrollment analytics dashboard was checked periodically by the Analytics Coordinator for general sanity, but the specific year-over-year grade trends were only reviewed at the start of the annual Title I funding application process in October. Between May and October 2026, several factors masked the problem:

First, the grade-level filter in the dashboard showed 21 values, and a casual inspection of the list box might attribute the extra '1' through '9' values to a data entry or SIS migration artifact rather than a fundamental type mismatch. Second, charts that showed total enrollment across all grades — without a grade-level filter — were unaffected and continued to show correct totals (47,293 current-year students appeared in the table and were counted in total-enrollment KPIs that did not apply a GradeLevel filter). Third, the reload completed nightly without errors, so no operational alert was raised. The gap was only revealed when a grade-specific trend chart was examined carefully enough to notice that the current year’s bar was missing from the visualization.

The retainer developer began diagnosis on October 8, 2026 at 09:30.

Diagnosis: list box values, Script Editor, and Table Viewer

-- Diagnosis Step 1: Count distinct GradeLevel values in list box
-- QlikView list box on sheet "Enrollment Overview":
-- GradeLevel listbox shows 21 values (expected: 12)
-- Values displayed: 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7, 8, 9

-- Confirm with quick count expression in a chart:
COUNT(DISTINCT GradeLevel)
-- Result: 21

-- Diagnosis Step 2: QlikView Table Viewer (Ctrl+T)
-- ENROLLMENT_FACT: 894,586 rows (correct — 847,293 QVD + 47,293 SQL)
-- GradeLevel field: 21 distinct values
-- Key: ENROLLMENT_FACT connects to DIM_STUDENT (StudentID), DIM_CALENDAR (SchoolYear)
-- No synthetic keys visible (all joins are on distinct key fields)

-- Diagnosis Step 3: Script Editor — check QVD LOAD field types
-- QVD LOAD: LOAD ... GradeLevel ... FROM Enrollment_Historical.qvd (QVD)
-- QVD field inspection (via QVD header tool):
--   GradeLevel: Type=ASCII, Length=2, Format='@2' (CHAR(2) right-padded string)
--   Loaded as: STRING — values are character strings: '01','02',...,'12'

-- SQL LOAD: SQL SELECT ... GradeLevel ... FROM dbo.vw_EnrollmentActive
-- SQL Server column: GradeLevel INT NOT NULL
-- QlikView type at load: NUMERIC — values are integers: 1, 2, ..., 12
-- Stored as dual: (numeric=1, text='1') through (numeric=12, text='12')

-- Root cause confirmed:
-- QVD source: GradeLevel='01' → QlikView dual (numeric=1, text='01')
-- SQL source:  GradeLevel=1   → QlikView dual (numeric=1, text='1')
-- CONCATENATE merges both: 21 distinct dual values in ENROLLMENT_FACT.GradeLevel
-- Grades 10, 11, 12: same text representation in both sources → 3 common values
-- Grades 1-9: zero-padded CHAR(2) vs plain integer → 9 split pairs → 18 values
-- Total: 3 + 18 = 21 distinct values

The diagnosis took 1.0 hour. The root cause was unambiguous: the QVD generator script was built against the legacy SIS’s zero-padded CHAR(2) GradeLevel format, and the Infinite Campus migration had switched the field to a plain INTEGER without updating the QVD load or the downstream expressions. The fix was straightforward.

The fix: TEXT(NUM#()) type coercion in the QVD LOAD statement

The goal was to ensure that GradeLevel values from the QVD source and from the Infinite Campus SQL source used identical dual representations before the CONCATENATE merged them. There were two options: normalize the historical QVD values to unpadded format, or normalize the SQL values to zero-padded format. Normalizing to unpadded format was simpler, since the Infinite Campus SQL already returned unpadded values, and existing Set Analysis expressions could be updated in one batch find-and-replace from {'01'} to {'1'}. However, a faster fix — with zero dashboard expression changes — was to normalize the SQL source to zero-padded format instead.

The approach chosen was to normalize the QVD source to unpadded format using QlikView’s TEXT(NUM#()) coercion function in the QVD LOAD statement. NUM#('01') parses the string '01' as a number, returning the numeric value 1 with string component '1'. TEXT() wraps the result to ensure the return type is purely the string representation of the number, not a dual with a format string. The result is that TEXT(NUM#('01')) returns the QlikView dual (numeric=1, text='1') — identical to what the SQL INTEGER 1 produces. After this coercion, both sources produce the same dual representation for all 12 grade levels.

-- BEFORE fix: QVD LOAD does not coerce GradeLevel type
ENROLLMENT_FACT:
LOAD
    EnrollmentID,
    StudentID,
    GradeLevel,            // raw QVD value: '01','02',...,'12' (CHAR(2))
    SchoolCode,
    EnrollmentDate,
    ExitDate,
    SchoolYear
FROM [$(vQVDPath)\Enrollment_Historical.qvd] (QVD);

-- AFTER fix: QVD LOAD coerces GradeLevel using TEXT(NUM#())
ENROLLMENT_FACT:
LOAD
    EnrollmentID,
    StudentID,
    TEXT(NUM#(GradeLevel)) AS GradeLevel,   // '01' → NUM#='1' → TEXT='1'
    SchoolCode,                               // '09' → NUM#='9' → TEXT='9'
    EnrollmentDate,                           // '12' → NUM#='12' → TEXT='12'
    ExitDate,
    SchoolYear
FROM [$(vQVDPath)\Enrollment_Historical.qvd] (QVD);

-- No change to SQL CONCATENATE load required.
-- Infinite Campus INTEGER 1 already produces dual (numeric=1, text='1').
-- After fix: QVD GradeLevel and SQL GradeLevel both produce identical duals.
-- ENROLLMENT_FACT.GradeLevel distinct values: 12 (not 21).

-- Set Analysis expressions already using {'01'} format must be updated to {'1'}:
-- Find:    {}   Replace: {}
-- Find:    {}   Replace: {}
-- ...
-- Find:    {}   Replace: {}
-- Grades 10, 11, 12: expressions already use {'10'}, {'11'}, {'12'} — unchanged.

The script fix was applied in QlikView Desktop and tested in the development environment before deploying to QlikView Server. The TEXT(NUM#()) coercion is applied at load time against the QVD data in memory — the QVD file itself is not modified, and no QVD regeneration is required. After the fix, the full QVW reload ran at 01:30 AM and produced 12 distinct GradeLevel values in the ENROLLMENT_FACT table.

All 14 grade-level Set Analysis expressions in the dashboard were updated from zero-padded format ({'01'} through {'09'}) to unpadded format ({'1'} through {'9'}). Expressions for grades 10, 11, and 12 required no change since their text representations were already consistent between the two sources. After the expression updates, all enrollment trend charts displayed correct year-over-year figures including FY2025–2026 enrollment for all 12 grade levels.

-- Post-fix validation: October 8, 2026, 10:48 AM

-- QlikView list box GradeLevel distinct value count:
COUNT(DISTINCT GradeLevel)
-- Before fix: 21
-- After fix:  12  ✓

-- Enrollment count by GradeLevel (FY2025-2026):
COUNT({} DISTINCT StudentID)
-- Before fix: 0   (GradeLevel='1' was in listbox but matched zero students
--                  because historical data was under '01' and no chart filter matched '1')
-- After fix: 3,847  ✓

-- Grade-level totals cross-check vs Infinite Campus SIS direct query:
-- Infinite Campus: SELECT GradeLevel, COUNT(*) FROM vw_EnrollmentActive
--                  WHERE SchoolYear = 2026 GROUP BY GradeLevel ORDER BY GradeLevel
-- SIS GradeLevel 1: 3,847   QlikView GradeLevel '1': 3,847  ✓
-- SIS GradeLevel 9: 4,102   QlikView GradeLevel '9': 4,102  ✓
-- SIS totals: 47,293         QlikView total: 47,293 ✓ (total enrollment was always correct)

-- Title I application validation:
-- FY2024-2025 vs FY2025-2026 grade-level comparison in dashboard:
-- Grade 1: 3,921 (FY25) → 3,847 (FY26): -2.0% (expected: -1.9% per enrollment projection)  ✓
-- Grade 9: 4,289 (FY25) → 4,102 (FY26): -4.4% (expected: -4.2% per enrollment projection)  ✓
-- All 12 grades show correct year-over-year trend (no anomalous drops).

The Title I funding application was resubmitted with correct enrollment data. Total engagement time: 3.5 hours (1.0h diagnosis: list box count inspection, Script Editor QVD vs SQL type comparison, Table Viewer verification; 1.5h fix: script modification, QVW reload in QlikView Desktop, 14 Set Analysis expression updates, test reload on QlikView Server; 0.5h post-fix validation with Analytics Coordinator; 0.5h documentation and retainer SOP update adding quarterly QVD field type audit as a recurring deliverable).

Why QVD field type drift is a recurring QlikView retainer problem

The CVSD incident is a specific instance of a class of QlikView retainer problems that share the same root: the QVD layer was built against a source system’s schema at a point in time, and a subsequent change to the source system’s field types or value formats was not propagated to the QVD load script. QVD files are schema-frozen at the time they are generated — the field metadata (type, format, length) stored in the QVD header reflects the source query at the time of the last QVD generation. If the source table’s column type changes from CHAR(2) to INTEGER, the existing QVD file continues to hold CHAR(2) values until the QVD generator script is re-run with updated field handling. A QVD LOAD statement reads the QVD as-is, preserving the stored types.

The CONCATENATE pattern that caused the CVSD problem is extremely common in QlikView: a QVD generator produces historical data with one type representation, and an incremental SQL load provides current data with a different representation. This pattern is architecturally sound — it enables fast incremental loads without rebuilding the full historical QVD each day. But it creates a coupling between the QVD’s stored type format and the SQL query’s current type format. Any source system migration that changes a field’s data type (VARCHAR to INTEGER, CHAR to NVARCHAR, padded numeric string to integer) will silently break the CONCATENATE unless the QVD load is updated to normalize the mismatch.

The properties that make QVD type drift difficult to detect without a retainer review are: no error or warning is produced at reload time (QlikView’s CONCATENATE accepts mixed types and stores both representations); charts that aggregate across all values of the mismatched field still produce correct totals (since both '01' and '1' are present and counted); only charts that filter by the mismatched field expose the discrepancy; and the discrepancy only becomes visible during reports that compare current-year to historical data across that dimension. A QlikView developer retainer who reviews QVD field type specifications quarterly — particularly comparing QVD header metadata against the current live schema for any fields used as dimensions or Set Analysis filters — catches this class of issue before it produces a reporting error.

How HourTab helps QlikView developer retainers

The Cascade Valley School District engagement totaled 3.5 hours: diagnosis (1.0h — 0.25h counting distinct GradeLevel values in the QlikView list box and confirming 21 vs expected 12; 0.25h reviewing the reload script in QlikView Script Editor, identifying the QVD LOAD statement with no type coercion and the SQL CONCATENATE pulling from Infinite Campus INTEGER column; 0.25h using the QVD header inspection tool to confirm QVD Enrollment_Historical.qvd stores GradeLevel as ASCII CHAR(2) format '01'–'12'; 0.25h tracing the discrepancy through Table Viewer and confirming ENROLLMENT_FACT shows 21 GradeLevel values with 47,293 current-year students associated to '1'–'9' and 847,293 historical students associated to '01'–'09'), fix (1.5h — 0.5h adding TEXT(NUM#(GradeLevel)) AS GradeLevel to the QVD LOAD statement in QlikView Desktop, testing the coercion formula in the script debugger, verifying ENROLLMENT_FACT reduces to 12 distinct GradeLevel values in the development reload; 0.5h updating all 14 grade-level Set Analysis expressions in the QVW from zero-padded format to unpadded format across enrollment trend charts, KPI objects, and pivot table dimensions; 0.5h deploying updated QVW to QlikView Server Access Point, triggering a Publisher reload task, and monitoring reload task completion), and validation and documentation (1.0h — 0.5h cross-validating GradeLevel counts from post-fix QlikView against a direct Infinite Campus SIS query across all 12 grade levels; 0.25h coordinating with the Analytics Coordinator to confirm the Title I funding enrollment figures are correct; 0.25h updating the retainer SOP to add a quarterly QVD field type audit for all CONCATENATE load patterns in the CVSD_EnrollmentAnalytics.qvw reload script).

In a HourTab retainer log, the incident session reads: “QVD CONCATENATE GradeLevel type mismatch: Enrollment_Historical.qvd CHAR(2) '01'–'12' + Infinite Campus INTEGER 1–12 → 21 distinct dual values in ENROLLMENT_FACT.GradeLevel (expected 12); Set Analysis {<GradeLevel={'01'}>} misses all 47,293 SY2025-2026 students for grades 1–9; 14 grade trend charts show K–9 enrollment at zero for FY2025-2026; root cause: Infinite Campus migration May 2026 changed GradeLevel from CHAR(2) to INT, no QVD LOAD update; fix: TEXT(NUM#(GradeLevel)) AS GradeLevel in QVD LOAD normalizes '01' to '1' at load time; no QVD regeneration; 14 Set Analysis expressions updated '01'→'1' through '09'→'9'; post-fix: 12 GradeLevel values; all FY2025-2026 grade counts correct; Title I application data validated; 3.5h.” The district’s technology director sees the root cause (SIS migration changed field type, QVD load not updated), the scope of impact (all 14 grade-level trend charts, FY2025-2026 data invisible for 5 months), and the fix applied at both layers (QVD LOAD coercion and Set Analysis expression update) — with no ambiguity about how the retainer time was spent.

HourTab turns a time-tracker CSV into a public retainer-hours URL your client can bookmark — no login, no portal, no “how many hours do I have left?” emails. Free for one retainer. $9/mo for up to ten.

FAQ: QlikView developer retainers

See also: Tableau developer retainer · MicroStrategy developer retainer · TIBCO developer retainer