Qlikview Lookup() Function

Doing a VLookup in Qlikview is not as difficult as it first seems. When you start trawling the web, you’ll find people suggesting you use the ApplyMap function. Lookup is similar to ApplyMap but the number of fields can be greater than 2 in the source table. The first step is to set up a mapping table:

DependencyList:
LOAD
ref, Dependent, [Target Date]
from [C:\Users\Documents\Programme Dashboard\Dependencies.xlsx](ooxml, embedded labels, table is [Risk]);

The next step is to add a lookup to your load script. The correct Syntax for the Lookup is:

Lookup('FieldName', 'KeyFieldName', KeyFieldValue [, 'TableName'])

I’ve included the full script below:

qlikview dashboard


DependencyList:
LOAD ref, Dependent, [Target Date]
from [C:\Users\Documents\Programme Dashboard\Dependencies.xlsx](ooxml, embedded labels, table is [Risk]);
LOAD ref,
[Risk Date Raised],
[Raised by],
Description,
[Risk Owner],
[Mitigation/ Contingency Actions],
Probability,
LOOKUP([Target Date], ref, Dependent, 'DependencyList') as Dependency1TargetDate,
LOOKUP([Target Date], ref, LOOKUP(Dependent, ref, Dependent, 'DependencyList'), 'DependencyList') as Dependency2TargetDate,
LOOKUP([Target Date], ref, LOOKUP(Dependent, ref, LOOKUP(Dependent, ref, Dependent, 'DependencyList'), 'DependencyList'),'DependencyList') as Dependency3TargetDate,
[Impact Level],
Status,
Dependent,
[Target Date],
RAG
FROM
[C:\Users\Documents\Programme Dashboard\Dependencies.xlsx]
(ooxml, embedded labels, table is Risk);

 

Image used under creative commons