Pick first value - similar to vlookup

[agoyal28]

Hi,

There are 2 tables in my database. First table contains only DEA IDs. Second table contains 2 more columns along with DEA ID. if you see there are multiple IOD value for a single DEA. Now i want to add a column of IOD to my first table (with DEA only) but only pick the first IOD value from table 2.

Here is the snapshot of my data

Table 1

DEA

BZ8028335

BZ8813164

BZ9422279

F95771983

Table 2

IOD	Primary DEA	HIN
Medical Practice	BZ8028335	57YMXLE00
SON - Physician Disp	BZ8028335	57YMXLE00
Medical Practice	BZ8813164	HBVT65R00
SON - Physician Disp	BZ8813164	HBVT65R00
Medical Practice	BZ9422279	BGEH8XR00
SON - Pharmacy Disp	F95771983	FJEVB5N00

Result

DEA	IOD
BZ8028335	Medical Practice
BZ8813164	Medical Practice
BZ9422279	Medical Practice
F95771983	SON - Pharmacy Disp

[LordNeilLord · Accepted solution]

Hey @agoyal28

Before you join Table1 to Table2 put a summarize tool on the Table2 stream....Group By Primary DEA and choose "First" IOD

[ponraj · Accepted solution]

Here is the sample workflow for your case. Hope this is helpful. 

Workflow