I have spent the last 24 hours trying to debug and then understand a strange issue I have been experiencing in my QVW script. Now that I feel I understand the issue enough to recreate and solve it, I feel I ought to share my findings in an effort to help others. I also feel as though some of you may have deeper knowledge and experience in this area which could further enlighten me.
The Problem
I have a data set, a large fact table, which has about 70 fields already. I need to add about 20 more fields in order to use the new fields in calculations involving many of the pre-existing fields. When I attempt a LEFT JOIN of these 20 additional fields directly from the Excel spreadsheet load I do not get any errors. Nevertheless, I get few and sometimes no actual joins. The new 20 fields become a part of the data set, but they are mostly empty.
Double Checking
I checked my primary key in many different ways and it was good. I even replicated my LEFT JOIN statement, aliased the fields differently, and used that statement to create a new test table (keeping the same key). I was able to select values in either the main table or the new test table and those selections caused expected corresponding filters to be applied to both tables. Observing this, I knew that my primary key was perfectly constructed.
Solving the Issue
After observing the behavior of the test table, I decided to comment out all of my LEFT JOIN statements which loaded directly from the Excel spreadsheets and work with the test table. I altered the aliases to what I originally wished the fields to be name. I added a LEFT JOIN on a RESIDENT load of the test table and I dropped the test table afterwards. This solved my problem. I was now left with a main data set having all 90 fields and the new 20 fields all had data in them. I switched back between this solution and the original problem script multiple times to prove the issue and solution to myself and to review it to colleagues.
Alternative Solution
Later, just before writing this, I decided to load the Excel spreadsheet data into a QVD first. I then attempted a direct LEFT JOIN during the QVD load. As I suspected, this also solved the issue.
Summary
- A LEFT JOIN during an Excel load I has serious data issues (few or no joins).
- A load from Excel to a temporary table, followed by a LEFT JOIN of a RESIDENT load of the temp table produces great results.
- A LEFT JOIN during a QVD load produces great results
The Question
Has anyone experienced this issue? Is the general guidance to avoid any JOIN behaviors during an Excel load?
