9 Replies Latest reply: Apr 23, 2015 2:04 PM by Nick Bogle RSS

    Resident loads not associating

    Nate Hallquist

      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_IDKey|EncIDDW_Enc_History_Max.EncIDDW_drg_Max.EncID
      64109876410987--
      -64109876410987-
      -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

        • Re: Resident loads not associating
          Dick Zeeman

          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

            • Re: Resident loads not associating
              Nate Hallquist

              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. 

                • Re: Resident loads not associating
                  Nate Hallquist

                  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?

              • Re: Resident loads not associating
                Rob Wunderlich

                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

                • Re: Resident loads not associating
                  Nate Hallquist

                  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.

                    • Re: Resident loads not associating

                      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

                        • Re: Resident loads not associating
                          Nate Hallquist

                          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.

                            • Re: Resident loads not associating

                              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.