Search Table Field For A Match In Script

[SimonDB]

Hi, hope you clever guys out there can give me some advice as I'm really struggling to do the following. I have a table loading in a script and want to do a lookup from one column to another and return the value in a different column if its a match. So to simplify if I have a table that looks like this:

RecNo	Description	RecordLink
1	Apple
2	Pear	Cherry
3	Banana
5	Orange
6	Berry	Apple
7	Plum	Orange
8	Cherry

I want to look for the value in "RecordLink" to see if matches a value in "Description", if it does return that "RecNo" in a new column like the below:

RecNo	Description	RecordLink	Link_RecNo
1	Apple
2	Pear	Cherry	8
3	Banana
5	Orange
6	Berry	Apple	1
7	Plum	Orange	5
8	Cherry

Thank you in advance, Simon.

[SimonDB · Accepted solution]

Solved this using LookUp

TEMP:
LOAD * Inline [

RecNo, Description, RecordLink
1, Apple,
2, Pear, Cherry
3, Banana,
5, Orange,
6, Berry, Apple
7, Plum, Orange
8, Cherry,
];

DATA:
LOAD *,
Lookup('RecNo', 'Description', RecordLink, 'TEMP') as Link_RecNo

Resident TEMP;