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.
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
5716 Berkshire Valley Road
Oak Ridge, NJ 07438
973-935-7223 ext. 884274
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.
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