this is my very first post in the Qlikview Community. I am not sure if this has been already discussed/answered... But I have seen a "top200" guy raising the same concern.
I have a problem that I can generalize and simplify with the following example...
Let's say that I have 2 tables in my DB: Customer and Country. The table Customer has, among others, 2 fields:
Customer.BirthCountryID
Customer.NationalityCountryID
Both are FK, referring to the PK field Country.CountryID
Now, here is my question: is there a way with Qlikview to avoid loading twice the table Country?
Of course, in my example, the table has many records (about 400.000), and I want to optimize the loading time and the disk space
BirthCountries: CustomerBirthCountryID, CustomerBirthCountryName 2, USA
NationalityCountries: CustomerNationalityCountryID, CustomerNationalityCountryName 3, France
This requires you to load two copies of the countries. The time and space for that is what concerns you, right?
The load time should be a non-issue. I might have a Country.qvw that did nothing but load the countries from my source database and dump them to a Country.qvd. So you'd only do the database reads once, not twice. You would then load your duplicate tables from the QVD. Loading 400,000 rows from a QVD only takes a few seconds on my ancient PC, and in my experience is even faster than a resident load. On any reasonable server, it should be done in a flash. Similarly, while I haven't tested it, I would expect QlikView's compression algorithm to realize that both tables were the "same", and thus take almost no additional space for the second table. So I don't think loading two copies of the country table should be a practical problem.
There is, however, another common data model for this sort of data that uses only a single copy of the country table:
Countries: CountryID, CountryName 2, USA 3, France
The data models are distinguished less by their load time and memory requirements and more by what questions they more easily answer. The first model is best for questions like "Which customers were born in Canada but have a nationality of England?" The second model is best for questions like "Which customers were either born in Mexico or have a nationality of Mexico?" The models CAN be combined to be able to answer both questions easily, but I think having three separate country fields to choose from could get pretty confusing, so I usually avoid that option.
If Country Name is the only attribute that you need from the Countries table, I'd recommend simply loading the Names into the same data table. Since you need to differentiate between the Birth Country and the Nationality Country, there is no way around duplicating this information.
Since QlikView only stores distinct values, the impact of this denormalization is minimal.
I figured there was more than a name in each table that was important, or yes, I'd likely combine everything together into a single table. I even link the names in when I build the QVDs, because we use a whole lot of codes, but we almost always want to display the names instead. I usually only make a developer load a supporting QVD if they need more than just the name associated with the code.
I am reading this post, which I sent at the very beginning of my Qlikview experience.
The answer that I was looking for was simply the Resident Load! At that time I did not know the meaning of "Resident load". Although John mentioned it, I thought that it was colloquial english, and I had no idea that it was a command!
Sometimes the beginners need a very basic help... so basic that you think that it's obvious, but for a beginner it is not 😉
