Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to Match values from a List?

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

3 Replies
Not applicable
Author

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

Not applicable
Author

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;

Not applicable
Author

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;