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

Lookup field value using variable (not inter-record)

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)?

1 Solution

Accepted Solutions
kmn
Employee
Employee

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

View solution in original post

2 Replies
kmn
Employee
Employee

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

Not applicable
Author

KM... Thank you for the prompt reply.

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