4 Replies Latest reply: Sep 29, 2015 7:46 AM by DataExploiter _ RSS

    External Lookups

    Frank Walka

      I have a situation where my external lookup file contains duplicate values on the linking column; however Monarch appears to be suppressing the duplicates. Is there way to disable the duplicate value suppression for the external lookup? I checked all the settings I can find and cannot seem to locate a setting that would do this.

       

      For Example....

       

      Excel File

      Account NumberName
      123456Frank
      123456Mary
      123456Steve

       

      External lookup table in Monarch

      Account NumberName
      123456Frank

       

      I would not want the lookup to suppress the duplicate account numbers so that my final output shows to all rows for account number 123456 with the varying names and additional data being mined from the model/report.

       

      Thank you!!

        • Re: External Lookups
          Grant Perkins

          Frank,

           

          Unless your "Master" account file has a way to store several entries in separate fields (e.g. multiple "Contact" fields might be relevant to your example) the lookup can only make one link and systems will usually default to the first one they come to.

           

          If your data receiving file has on a single field you would need to make the Excel input record contain all the possible values of that field.  So something like:

           

          123456      Frank, Mary, Steve

           

          If the receiving record has multiple fields then the table to be looked up also needs an identifier for the separate fields. So for example,

           

          Acct          Contact Field 1      Contact Field 2    Contact Field 3

          123456                        1                               2                           3

           

           

          Would use an Lookup table something like

           

          Acct           Contact Ref       Name

          123456                      1        Frank

          123456                      2        Mary

          123456                      3        Steve

           

           

          The lookup link would then be based on 2 fields, "Acct"    and then the Contact Field mapped to the Contact Ref and matched by number.

           

          On the other hand if you want to the functionality to take a master account record and then expand the number of records for the account based in the number of lookup names associated with it you probably need to consider doing the lookup the other way around or, perhaps, reconsider what the objectives are to see if there is a different approach better suited to them.

           

          That is something I have had to do with projects from time to time. I recall one project where 3 separate data files had links to each other but not a complete set of usable links for all of them for my purposes.

           

          I had to pad each of the files with extra records at each stage of the linking in order to receive lookup data from the other sources. The final step was to take the separately created files making sure they all had the same record structures, and combine them into a single file, doing all the required analysis from the resulting Master Output file.

           

          Form your description it sounds like that may be the approach you need to take - but I can't be sure. It may be that you just need to look up on multiple fields.

           

           

          HTH.

           

           

           

          Grant

            • Re: External Lookups
              Frank Walka

              Thank you for the information.

               

              I decided to further develop the reporting tool that creates the data file for the lookup tool so that it only has one record per row and combines all the names into one row. Seems like an easier option than working with multiple lookups. I was really just wondering if there was a way to change the join properties so that It would return multiple results and not just the first record it hits .

               

              Thanks again for taking the time to answer!

               

              Frank Walka | Assistant Vice President

              Business Process Analyst

               

              Business Intelligence

              5716 Berkshire Valley Road

              Oak Ridge, NJ 07438

              973-935-7223 ext. 884274

              fwalka@lakelandbank.com<mailto:fwalka@lakelandbank.com>

              <http://www.lakelandbank.com/>

                • Re: External Lookups
                  Grant Perkins

                  Hi Frank,

                   

                  I think what you seek is useful but goes beyond a basic lookup concept since you would need to be writing more records than are contained in the original file to which you are adding the lookup data.

                   

                  That is more of a pre-planned "create and merge" than a basic lookup - in fact really just like the sort of thing I described for my project example.

                   

                  If taking data to a new analysis file that becomes a powerful approach.

                   

                  If the end data is to be processed back into an existing data file it may not be possible to simply import the new version of the file with added records. Even using the solution of multiple names (your example) into a single row (or perhaps database field in some situations) can hit constraints like field sizes.

                   

                  I'm guessing you know this but I mention it here for those who are perhaps quite new to this sort of activity.

                   

                  On of the great things about Monarch is that is is easy enough to take either approach depending on how the needs pan out.

                   

                  It's probably worth keeping an eye on the concepts developed around the general of the Data Prep functionality to see how that progresses.

                   

                  Assuming the resulting output files is required for analysis purposed and not so much for ETL activity the potential for generating output files with additional records (compared to the original "Master" file) can offer some useful opportunities and efficiencies.

                   

                   

                  Grant

              • Re: External Lookups
                DataExploiter _

                Monarch 13 Complete with the data prep studio allows more than just a simple left outer join, default  lookup for Monarch basic. But instead full join,inner join, right join http://www.dofactory.com/Images/sql-joins.png That may help, John