Do You Clean the Same Data Again in Every Qlik Load Script?
The same mapping loads, string fixes and deduplication are written again in every app and QVD layer, and the copies drift apart. The cleansing is repeated per script because no integrated warehouse layer does it once.
The data warehouse automation platform trusted by data teams across industries
Does this sound familiar?
- The same ApplyMap and Trim logic sits in several load scripts, and the copies no longer match.
- A new country code in the source means editing the mapping table in every app that uses it.
- Two apps show different customer counts because they deduplicate the same source differently.
- Nobody is sure which script holds the current version of a cleansing rule.
One app cleaning raw data is a workaround. Ten apps cleaning the same raw data, each a little differently, is a second warehouse that nobody designed.
Where the same cleansing is written again
- Every app copies the mapping. The same
MAPPING LOADandApplyMapfor country, product and customer codes, pasted into each script that needs the field. - Every copy drifts. One script trims and upper cases, another does not, a third added a new code last month. Same field, different values.
- Every QVD layer repeats it. Extract, transform and QVD generator apps clean the data, and the next app cleans it once more to be safe.
- Every change is a search. A new source code has to be found and fixed in each script, and the one that was missed fails silently.
An $(Include) file keeps the text identical. It does not stop each app from deciding whether,
when and in which order to use it.
Why it lands on you
- Each app was built when it was needed, and copying the fixes from the last script was the fastest way to ship it.
- Nobody owns the rule, so nobody notices when two scripts start to disagree.
- You can align the scripts you maintain. You cannot stop the source from sending raw data to all of them.
The work is in the wrong place
- Cleansing source data is warehouse work. In Qlik it is written again in every load script that touches the field.
- It belongs in one central place: a data warehouse or data hub, built once and read by every app.
- If that sounds expensive, the estimate probably assumes hand built pipelines. A specialised automation platform generates them from a model, which changes both the cost and the time it takes.
What changes when the model arrives finished
Datavault Builder generates the integrated, historised vault and the dimensional layer on top, natively in the database you already run, from SQL Server and Azure SQL to Snowflake or BigQuery.
- Each cleansing rule is defined once, so a code mapping changes in one place, not in every script.
- Duplicates are resolved on integration, keyed on business keys rather than filtered per app.
- History is kept as satellites, so no QVD has to preserve yesterday’s version.
- The load script becomes a plain
LOADfrom cleansed tables, short enough to read. - Lineage shows each transformation, so a changed rule can be traced before it is deployed.
What to ask for
Not “the transform script is a mess”, but:
“We clean and map the same source fields in several QVD generator apps, and they no longer agree. Can that cleansing live in an integrated warehouse layer, so our apps load finished dimensions instead of raw extracts?”
If building that layer sounds like a year of pipeline work, the estimate assumes it is written by hand. That is the part Datavault Builder generates.
See It Working on Your Own Data
Book a free demo and bring the report that gives you the most trouble.
Three Steps to Numbers That Add Up
-
Extract the existing logic
Collect the calculations, joins and filters that live in your reports today.
-
Centralize it in one place
The logic moves into the warehouse model once, so every report reads the same definition.
-
Enjoy numbers that add up
Every report shows the same figure, and “where did this number come from” has a visible answer.
How Datavault Builder Hands Your Report a Finished Model
-
The model arrives finished
Datavault Builder generates the vault and the star schema to run natively in the database you already have: SQL Server, Azure SQL, Synapse, Fabric, Snowflake, Databricks or BigQuery.
-
The work leaves the report
No merge, no parsing, no fuzzy match. That work is gone from the report.
-
Sources arrive integrated
Customers from the ERP, the CRM and the web shop are matched into one set of conformed dimensions. The join happens once in the warehouse, not again in every report.
-
History you can query
Every change is retained as it arrives, so you can report as-was as well as as-is, even where the source system overwrites its own records.
-
Every number has lineage
The logic gains lineage, so “where did this number come from” has a visible answer.
-
Changes handled upstream
Slowly changing dimensions are handled upstream as vault satellites, not approximated.
Recognized by BARC in The Data Fabric Survey 26
Meet Our Expert
Twenty minutes with our Sales Director, and an honest answer on whether this fits your situation.
Matt Collett
Sales Director
Great, pick a time that works for you:
Other Problems This Series Covers
-
Do Your Qlik Apps and Power BI Reports Show Different Numbers?
Neither Qlik nor Power BI is wrong. Each has its own load logic, its own definitions and its own lineage, so the same metric is calculated twice and nobody can reconcile them. The rules belong in one governed warehouse model both tools read.
-
Are Your Qlik Set Analysis Expressions Too Long and Too Slow?
Set analysis is precise for genuine comparisons. Most of the long expressions in your app are there because the model never delivered history, flags or a single grain, so the chart rebuilds them on every selection.
-
Do Your Qlik Apps Keep Creating Synthetic Keys?
Synthetic keys and circular references are Qlik associating exactly what it was given. They appear because the data arrives without conformed dimensions or real keys, so every app has to invent them.