How to Match values from a List?

[#1]

Dear,

I am looking for a way to lookup a value in a list in Qlikview (like a vlookup in Excel).

I tried with the Match function but this doesn't seem to work.

I have 2 table's that both contain a field ID (ID1 and ID2). Want I want to do is check if the value ID2 appears in the list of ID1. If so I would want it to return a 1. Like this I will be able to see which ID's got migrated correctly and which ones didn't.

Does anyone know how to achieve this with Qlikview?

Thanks in advance!

Kind regards,

Jens

[#2]

You can use the Where exists in a new table (maybe a temporal one) that concatenate the two tables and that way you can know the ID that exist in the ID1 from the ID2: I make something like this:

ID1:

load * inline

[

ID1

1

2

3

4

5

6

];

ID2:

load * inline

[

ID2

1

2

3

4

5

6

7

8

9

];

NoConcatenate

Validate_ID:

LOAD Distinct ID1 as IDs Resident ID1;

          inner join

LOAD Distinct ID2 as IDs Resident ID2

where Exists(ID1,ID2);

Hope it Helps.

regards

[#3]

Juan is on the right track, but it sounds like you are looking for a flag instead of limiting the load.

ID1:

LOAD * INLINE [

     ID1,

     1,

     2,

     3,

     4,

     5

];

ID2_temp:

LOAD * INLINE [

     ID2,

     1,

     2,

     3,

     4,

     5

];

ID2:

LOAD ID2

     ,If( Exists(ID1,ID2),1,0) as ID1_Flag

RESIDENT ID2_temp;

DROP TABLE  ID2_temp;

[#4]

Hope it helps!

Production:
LOAD Field1,
     Field2,
     Field3,
     Field1&Field2&Field3 AS %Key1
FROM
Table1.qvd
(qvd);

Development:
NoConcantenate
LOAD Field1,
     Field2,
     Field3,
     Field1&Field2&Field3AS %Key2 
FROM
Table2.qvd
(qvd)
WHERE NOT EXISTS(%Key1,Field1&Field2&Field3)
;
Drop Table Production;