Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Good Morning Community. I have a rather strange issue that I have never seen before, and I am hoping that someone else has. I am currently working in a standard 3 tier archetecture, loading everything from QVD's. We are working on Version 11.1 for Desktop. We are also working on a 64bit server with ample RAM and Processing power. Here is my issue.
I load a series of Tables (as QVD's) into an appliction just fine. I then go about the process of creating a Link Table model. We need the link table model becuase we often have several fact tables. Everything loads just fine, except that the Link Table keys won't join. I create table boxes to check the keying and I get a key for each entry in the link table, but the fields in the actual tables, don't join. I get results that look like this.
Enc_ID | Key|EncID | DW_Enc_History_Max.EncID | DW_drg_Max.EncID |
6410987 | 6410987 | - | - |
- | 6410987 | 6410987 | - |
- | 6410987 | - | 6410987 |
It should be noted here that here that we are building our LinkTable using Concatenate and Resident loads. As you can see above, Key|EncID is the KEy that is added to the LinkTable. It appears that the Keys are not associating correctly. I have also noted that if we try to do a Mapping Load, using a resident Load Statement, it doesn't link. The Keys just don't relate or Associate. Now, if we remove the link table, the Keys will link fine and everything works great. So, it is my belief that this issue lies somewhere with a resident load. This is the only thing I can think of becuase the Link Table and the Resident Mapping loads are not joining. Oh yeah, there are 3 of doing developement at this location, and none of us can get this link table to join, or use Resident Mapping loads.
I have gone through this application with a fine tooth comb, making sure my Keys are all named the same, I have used Number wrappers and LTrim/Rtrim to emilinate trailers. I have copied cell values to text files looking for hidden characters. I have gone through the permissions of the application to see if There is a weird special permission I missed. I have gone through the User and Document settings to see if I missed something there. We have even gone back to the source system and checked the Output for the data type in the Database and made sure that they are all the same.
Has anyone else seen anything like this? We have other customers that we use LinkTable models with and have never seen this before, in versions before 11.1 and after 11.1.
Any help is appreciated.
Nate
Without sample code it's hard to say, but it does look like you are using different fieldnames for the same key:
Enc_ID
Key|EncID
DW_Enc_History_Max.EncID
DW_drg_Max.EncID
are 4 different field names. Even with concatenate you do need to use the same name for the same field, otherwise Qlikview will just add another field to the table
The code looks like this...
EncHistoryMax:
Load
Field1 as Key|EncHistUniqueID,
DW_Enc_History_Max.EncID,
Field3
From QVD;
LinkTable:
Load Distinct
Key|EncHistUniqueID,
DW_Enc_History_Max.EncID as Key|EncID
Rexident EncHistoryMax;
DRGMax:
Load
Field1 as Key|DRGUniqueID,
DW_drg_Max.EncID,
Field3
From QVD;
Concatenate (LinkTable)
Load
Key|DRGUniqueID,
DW_drg_Max.EncID as Key|EncID
Resident DRGMax;
The Sample code above is all I have at this time. The machine the actual code is on in a different network and I can't get it out easily.
A recent update... I took my three fact tables and keyed them to associate to the each other directly, and not through a link table. It worked perfectly. Below are the results.
dw_enc_history_max.ENC_ID | dw_drg_max.ENC_ID | Key|EncID | dw_enc_pat_dtl.ENC_ID |
6410987 | 6410987 | 6410987 | 6410987 |
The trouble with this is that there is a 3-way association in the data model then. The other bit of trouble is that I am likely going to add yet another fact table with EncID as a potential key. That will give a 4 way join, which is not good. I'd like to have a Link Table model, bucause it is easier to work with.
Does anyone else have thoughts on this? Why would something link/associate fine from QVD, but not seem to link using resident loads and a Link Table?
It looks very much like type mismatch. All your keys look like numbers but they may be texts in one of tables (e.g. link table) and therefore won't join. Also I would be reluctant to use rare symbols like '|' in field names.
PS. If you need to inspect resident tables there is a useful trick.
You didn't show your MAPPING LOAD from RESIDENT code, but one thing to keep in mind is you cannot concatenate -- implicitly or explicitly -- to a MAPPING table. And you cannot replace it.
-Rob
I figured it out. I am still a little new at the Link table model and was not propwerly adding Foreign Keys. This is resolved.
Nate,
I am having a similar problem. For some reason, my product data is no longer linking to my sales data. I use a link table model as well. Can you expound a little further on what you meant by "not properly adding Foreign Keys".
-Nick
Hi Nick- This happened quite a while ago, and I have moved on from that customer and don't have access to the code. However, since then, I have moved away from Link Table Models. Currently, we are deploying this scenario as a Concatenated Fact table instead. We find that the performance is much better, and it is easier for our customers to understand and maintain.
If you are interested in moving away from the Link Table and into a Concatenated Fact table, I'd be happy to help you out. Sorry I don't have more help for you. Maybe Rob can help you more.
Thanks for the quick response. I think I figured it out now, I have a temp table from a resident load that was getting dropped prior to my script that created the composite keys relating to my product attributes. I moved the drop script to after the composite key script and it seems to be working now.