Resident loads not associating

[hallquist_nate]

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

[#2]

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

[hallquist_nate]

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. 

[hallquist_nate]

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?

[#5]

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.

[rwunderlich]

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

[hallquist_nate]

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.

[#8]

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

[hallquist_nate]

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.

[#10]

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.