topic How to Match values from a List? in QlikView

How to Match values from a List?

Fri, 01 Jun 2012 08:25:04 GMT

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

How to Match values from a List?

Fri, 01 Jun 2012 18:05:45 GMT

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

];

ID2:

load * inline

[

ID2

];

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

Re: How to Match values from a List?

Mon, 16 Jul 2012 20:49:23 GMT

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,

];

ID2_temp:

LOAD * INLINE [

ID2,

];

ID2:

LOAD ID2

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

RESIDENT ID2_temp;

DROP TABLE ID2_temp;

Re: How to Match values from a List?

Tue, 31 Jul 2012 13:12:20 GMT

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;