topic Re: Lookup field value using variable (not inter-record) in QlikView

Lookup field value using variable (not inter-record)

Tue, 05 Nov 2013 20:28:59 GMT

I'd appreciate ideas on a solution for how to perform a lookup of a field value in a Table, but not for an inter-record lookup (where you could use Lookup() or Exists(), but rather when the value you want to lookup is in a script variable.

I am trying to load files using FOR Each File in filelist(...) and the number of files is quite large (many 100's). I have a table that contains files I want to exclude (table is called 'ExcludeFiles' and has a column called 'FileName'). So I would like to do this:

FOR Each File in filelist('....')

IF IsNull(Lookup( 'ExcludeFile', 'ExcludeFile', '$(File)', 'ExcludeFiles')) Then

... process the file

END IF

NEXT File

However, Lookup() is not working here. Nor could I get Exists to work. I gather because both are intended for inter-record lookups.

Any ideas on how I can look for a match in a resident table when the matchvalue is in a variable (such as $(File) in this case)?

Re: Lookup field value using variable (not inter-record)

kmn — Tue, 05 Nov 2013 20:58:39 GMT

If your "exclude" table is called "ExcludeFiles" and its field/column that has the excluded values is called FileName then this should do the trick:

FOR Each File in FileList()

IF len(LookUp('FileName','FileName',File,'ExcludeFiles')) =0 THEN

LOAD

'$(File)' as File

AUTOGENERATE 1;

ENDIF

NEXT File

-Kmn

Re: Lookup field value using variable (not inter-record)

Tue, 05 Nov 2013 21:31:32 GMT

KM... Thank you for the prompt reply.

I made a careless error, and you pointed me right to it... thanks so much