Lookup value from another table

[Anonymous]

Hi there,

I have what I think is a common question.

Simplified: I have two tables and want to pick/lookup/peek a value from TableA to TableB when scripting. I have a key. I have read about the functions but can't get which one to use and how to use them.

TableA

Key	Value
1	b
2	c
3	d

I tried this...

TableB:
LOAD
     Field1 As MyKey,
     Lookup(Value, Key, MyKey, TableA) As ValueFromTableB
     Field3 ...
FROM MySource

I would like to accomplish a load where I can get the value from table B depending on which key I have in the current record from MySource.

Thanks in advance for your help!

Best,

Filip

[sunny_talwar · Accepted solution]

Try with single quotes around 1st, 2nd and 4th argument of Lookup function and original field name for MyKey (Field1)

TableB: 

LOAD Field1 As MyKey,

          LookUp('Value', 'Key', Field1, 'TableA') As ValueFromTableB 

          Field3 ... 

FROM MySource

There is an example given for lookup in help section: QlikView ‒ LookUp - script function

[MK_QSL]

Never look down – The Lookup() Story

[vikasmahajan]

use apply map instead of lookup

TEST1:

LOAD * INLINE

[Field

1

2

3 ];

MAP:

mapping LOAD * INLINE

[ Field,FiledVAlue

1,P1

2,P2

3,P3

];

TEST:

LOAD

ApplyMap('MAP',Field) as Field1

Resident TEST1;

DROP Table TEST1;

[sunny_talwar]

Applymap is a good idea, but the only problem could be if there are more than 2 column in the table we are looking the value from. So if there are only 2 columns in Table A (as provided by OP) then I would definitely vouch for ApplyMap, but if there are more than 2, ApplyMap can't be used without doing a Resident Load of the data into a table which only has two columns.