How to remove a duplicate from a lookup or only return one of the results?

[bmkaiser]

I am performing a lookup on a table that contains data that I don't manage and cannot change. The lookup is returning duplicates for some people because they're listed twice in the source table. How do I either return only one of the results from the lookup or remove the duplicate from only the affected records? My data looks something like this:

Name     Dept
----------------------
Joe       IT
          IT
----------------------
Mary      IT
----------------------
Bob      Ops
----------------------

I don't want to remove all occurrences of IT. I only want to remove the duplicates from Joe's row. Or perhaps there's a different way I can get the information so that it is only returning one result. The lookup portion is pretty standard and looks like this:

| LOOKUP name AS first_name OUTPUT department AS Dept

[bmkaiser]

I did also figure out how to remove the duplicate using mvdedup:

| EVAL Dept=MVDEDUP(Dept)

[JWellsBNSF]

This was incredibly helpful. Thank you.

[cramasta · Accepted solution]

Create a lookup definition.

manager > lookups > lookup definitations

check advanced options then change the maximum matches to 1

so instead of

| LOOKUP mylookup.csv name AS first_name OUTPUT department AS Dept
you use
| LOOKUP mylookupdefinition name AS first_name OUTPUT department AS Dept

[cramasta]

you can also dedup the lookup file by running the a query as such.

| inputlookup mylookup.csv | dedup name | outputlookup mylookup.csv

[bmkaiser]

Thank you for your help! I like this because I'd rather never have the data in the first place.