1 Reply Latest reply: May 15, 2014 9:55 AM by Grant Perkins RSS

    External lookup fails

    Lanette _

      I have a model created on a master report where I am doing an external lookup to a .csv file created from a supplementary report.  I am linking 3 columns from my master report to 3 columns from my lookup file.  The three columns in the master report are identical in name and data type to the source report, however there is one record in the master report that is not in the lookup report.  When I try to create the lookup, I get the following error message:

       

      The selected source columns do not form a unique key to the external table. Do you want to continue by using data from the first instance of each key?[/b]

       

      If I select "Yes" at this message, I get another error message saying:

       

      An external lookup data source 'F:...." is either missing or invalid.  From the Table Menu choose Data/External Lookups to select a new data source for the offending lookup.[/b]

       

      For some reason this does not happen if I export my supplementary data into an Excel file and do a lookup to it...when it joins on the record in the master report that is not in the lookup file, I just get Nulls in the columns I am bringing in.

       

      Any idea what causes this?  Is there a setting I can use to get it to ignore missing lookup data? Due to the fact that the amount of data in my reports will usually exceed the Excel maximum number of rows allowed, I cannot do a lookup to an Excel file.

       

      Help please!   :confused:

        • External lookup fails
          Grant Perkins

          Hi Lanette,

           

          This does sound odd.

           

          What is the missing data? If you are getting NULLS (or are they really blanks?) from the Excel source is it possible that the excel table has a blank line that is treated as a real data source whereas the csv file has no equivalent and so fails to populate the database and gives an error.

           

          A long shot but worth a try.

           

          Grat

           

           

          Originally posted by Lanette:

          I have a model created on a master report where I am doing an external lookup to a .csv file created from a supplementary report.  I am linking 3 columns from my master report to 3 columns from my lookup file.  The three columns in the master report are identical in name and data type to the source report, however there is one record in the master report that is not in the lookup report.  When I try to create the lookup, I get the following error message:

           

          The selected source columns do not form a unique key to the external table. Do you want to continue by using data from the first instance of each key?[/b]

           

          If I select "Yes" at this message, I get another error message saying:

           

          An external lookup data source 'F:...." is either missing or invalid.  From the Table Menu choose Data/External Lookups to select a new data source for the offending lookup.[/b]

           

          For some reason this does not happen if I export my supplementary data into an Excel file and do a lookup to it...when it joins on the record in the master report that is not in the lookup file, I just get Nulls in the columns I am bringing in.

           

          Any idea what causes this?  Is there a setting I can use to get it to ignore missing lookup data? Due to the fact that the amount of data in my reports will usually exceed the Excel maximum number of rows allowed, I cannot do a lookup to an Excel file.

           

          Help please!    :confused:  /b[/quote]