Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;