12 Replies Latest reply: May 15, 2014 9:57 AM by gsims _ RSS

    Source Key Contains Duplicate Values

    Brie _

      I am trying to join two prt reports.  I have models for both and have exported one of the reports into excel and am using the other report in Monarch to join the excel file to.  I have combined two columns in both reports to create unique source keys to join -- however, I am still getting the "Source key contains duplicate values" error.  I have exported just the unique columns of both reports to excel and compared them and there are no duplicates in either report.  Any suggestions? 

       

      Thanks

       

      Brie

        • Source Key Contains Duplicate Values
          Grant Perkins

          Brie,

           

          This may be a silly question but hey, got to start somewhere.

           

          Do you perhaps have any blank or null records/cells in either report?

           

          Are the field sizes defined making use of the full de-duplicated field size?

           

          Would you be able to provide suitable samples of the two files (may mean doing a stand alone extract from both report files) so that I could experiment and see what happens?

           

          Which version of Monarch are you running?

           

          Grant

           

           

          Originally posted by Brie:

          I am trying to join two prt reports.  I have models for both and have exported one of the reports into excel and am using the other report in Monarch to join the excel file to.  I have combined two columns in both reports to create unique source keys to join -- however, I am still getting the "Source key contains duplicate values" error.  I have exported just the unique columns of both reports to excel and compared them and there are no duplicates in either report.  Any suggestions? 

           

          Thanks

           

          Brie /b[/quote]

          • Source Key Contains Duplicate Values
            Brie _

            Hi Grant -

            I am using version 6 still.  Yes, there are blank fields in both reports.  I don't understand the second question you asked - is that a version 7 function?  I can send you samples of both reports - what is the best way to get it to you?

             

            Thanks

             

            Brie

            • Source Key Contains Duplicate Values
              Brie _

              I am able to get past the "source key contains duplicate errors" message - but now I am getting a "join data source is either missing or invalid" message. 

               

              Brie

              • Source Key Contains Duplicate Values
                Grant Perkins

                Originally posted by Brie:

                I am able to get past the "source key contains duplicate errors" message - but now I am getting a "join data source is either missing or invalid" message. 

                 

                Brie /b[/quote]It sounds like it can't find a file/table to join to. Has the file name or location changed compared to the one you (presumably) saved in the model creation activity?

                 

                The second question was not specifically a V7 thing. I was just suggesting that it would be worth checking the fields you were using for comparison have the correct number of characters to work with. It was to do with field size versus data size and justification within the field.

                 

                You have moved beyond that by the sounds of it (was that the blanks?) so I think we can forget about it now. Probably a bit off-beam anyway.

                 

                Grant

                • Source Key Contains Duplicate Values
                  michaeljul _

                  The best way of determining the problem when you get the 'duplicate key' error, is to make a summary of your source data, where you only include the key field that you join on and then sum on count and sort the summary descending on count - that will show you at the top if you have blanks or other duplicates in the key field right away.

                  A solution to the problem is obviously to make a summary with all the fileds from the table and then export that to excel and then join to that instead. Hope this helps

                  • Source Key Contains Duplicate Values
                    Heather Overacker

                    I am getting an error message similar to Brie's, "join data source is either missing or invalid", except mine says, "An external lookup data source is either missing or invalid" when I am trying to add a new external lookup joining an Excel file.  The file name or location has not changed compared to the one I saved in the model creation activity (Grant's advice).  Here is what is really strange, when I try adding an external lookup to the SAME Excel file, but used column data different from what I actually need (just experimenting) , IT WORKS!!!  but I get the error message everytime I try to join the two data columns that I really need joined.  So I guess sometimes it can't find the same Excel file, and sometimes it can.........strange

                    help me, heather

                    • Source Key Contains Duplicate Values
                      Grant Perkins

                      As I recall you can get the same message if the file you are linking to is where the model thinks it is but is currently open for update. Might be worth checking that if only to eliminate the possibility. If it's not that then it likely to be something very similar, such as a slight difference in the file/table name for example, which is easy to miss.

                       

                      This off the top of my head and a bit of a guess based on something half remembered  - I think I have had that message before if somehow the data types being used for the link have become mis-matched. If not that effect and message then something similar.

                       

                      I could be wrong with both of those but they may be worth eliminating.

                       

                      Originally posted by Heather Overacker:

                      I am getting an error message similar to Brie's, "join data source is either missing or invalid", except mine says, "An external lookup data source is either missing or invalid" when I am trying to add a new external lookup joining an Excel file.  The file name or location has not changed compared to the one I saved in the model creation activity (Grant's advice).  Here is what is really strange, when I try adding an external lookup to the SAME Excel file, but used column data different from what I actually need (just experimenting) , IT WORKS!!!  but I get the error message everytime I try to join the two data columns that I really need joined.  So I guess sometimes it can't find the same Excel file, and sometimes it can.........strange

                      help me, heather /b[/quote]

                      • Source Key Contains Duplicate Values
                        Heather Overacker

                        I tried these suggestions and it is still not correcting the problem.  I made sure the file was closed when trying to link to it.  The file named in the model/external lookup is the same name of the Excel file.  Same with the table/worksheet tab names, they match.  I think it may have something to do with unmatching data types, so I tried to format everything exactly the same.  Still tells me the file is missing or invalid, but that isn't true because when i link different columns (not really needed) to the same file it works.  This is frustrating me.

                        Originally posted by Grant Perkins:

                        As I recall you can get the same message if the file you are linking to is where the model thinks it is but is currently open for update. Might be worth checking that if only to eliminate the possibility. If it's not that then it likely to be something very similar, such as a slight difference in the file/table name for example, which is easy to miss.

                         

                        This off the top of my head and a bit of a guess based on something half remembered  - I think I have had that message before if somehow the data types being used for the link have become mis-matched. If not that effect and message then something similar.

                         

                        I could be wrong with both of those but they may be worth eliminating.

                         

                          /size[quote]quote:[/size]Originally posted by Heather Overacker:

                        I am getting an error message similar to Brie's, "join data source is either missing or invalid", except mine says, "An external lookup data source is either missing or invalid" when I am trying to add a new external lookup joining an Excel file.  The file name or location has not changed compared to the one I saved in the model creation activity (Grant's advice).  Here is what is really strange, when I try adding an external lookup to the SAME Excel file, but used column data different from what I actually need (just experimenting) , IT WORKS!!!  but I get the error message everytime I try to join the two data columns that I really need joined.  So I guess sometimes it can't find the same Excel file, and sometimes it can.........strange

                        help me, heather /b[/quote][/b][/size][/QUOTE]

                        • Source Key Contains Duplicate Values
                          Bruce _

                          Heather & Brie Just a thought that might help...

                           

                          Could the Excel file be open by another user or process?

                           

                          Excel does not like sharing files & Monarch is not great about reporting the exact error. Do you have access to Access? Try a test export to Access & see if you can view this new table in Monarch.

                           

                          Good luck.

                          • Source Key Contains Duplicate Values
                            Heather Overacker

                            The Excel program is not even open and my Excel file is not being used by another application.  I also tried to export the file to Access and then do an external lookup to it, but I am getting the same error message.  I really believe the problem may lie in the format/type of column data being linked, but I can't find the discrepancies.  I've checked everything suggested and that I can think of including font, etc.

                            • Source Key Contains Duplicate Values
                              Grant Perkins

                              Heather,

                               

                              This seems more and more of a puzzle.

                               

                              The correctly named file exists, the dat and data types look like they match and there are no security/password/access rights issues in play as I understand it.

                               

                              Have you tried re-creating the relevant part of the Excel sheet and then linking to that? (Process of elimination.)

                               

                              One other point that might be in play - and is raised in the Help file for the External lookup wizard - is the possibility for there to be both a worksheet and a named range in the Excel workbook with the same name but referring to different places.

                               

                              Here is the section;

                               

                              "Note:  When importing or joining data from an XLS file, the list of tables may include what appear to be duplicate table names, one with a dollar sign suffix.  The table name without the dollar sign represents a named range in the XLS file.  The name with the dollar sign represents the worksheet name.  Typically, both names will point to the same data, but in rare cases, the range name may point to a different data set.  When in doubt, select the name with the dollar sign suffix."

                               

                              It's a guess and a long shot but you seem to have covered everything else! Even if not the answer it prompt a new idea.

                               

                              Sorry I can't offer more ideas.

                               

                              Grant

                               

                              Originally posted by Heather Overacker:

                              The Excel program is not even open and my Excel file is not being used by another application.  I also tried to export the file to Access and then do an external lookup to it, but I am getting the same error message.  I really believe the problem may lie in the format/type of column data being linked, but I can't find the discrepancies.  I've checked everything suggested and that I can think of including font, etc. /b[/quote]

                              • Source Key Contains Duplicate Values
                                gsims _

                                I got the same error message about the data source being invalid or missing.  I had a similiar problem in Excel when trying to import from an external data source and the problem had to do with my user profile folder having a "." in it.  Since the file I was trying to join was on my desktop, the path to it was "C:Documents and Settingsgsims01.000Desktop".  When I moved the file into my C drive it worked fine.