QlikTip #3: Check whether a field exists or not (translated and improved)

If you want to load all fields in a table or a QVD file in loading scripts (so typically SELECT * FROM... scenarios), it is often necessary to know whether a field (within a table) exists or not.

The following code example demonstrates how this can be achieved:

Customers:
LOAD * INLINE [
    CustomerId, PostalCode, City
    1, 1010, Vienna
    2, 8020, Graz
    3, 6020, Innsbruck
    4, 9020, Klagenfurt
];

// Now we would like to know if the field '<code>Country</code>' exists in the table '<code>Customers</code>'
LET X = FieldNumber('Country','Customers');

if $(X) = 0 then
    // field does not exist
    TRACE field 'country' does not exist;
        /*
        add your code here ...
        */
else
    // field exists
    TRACE field 'country' exists;
         /*
        add your code here ...
        */
end if

Explanation:
The function FieldNumber returns 0 if a field does not exist.

Simplification for better reuse:

By using a “user defined function” (UDF) we can simplify the code above for easier reuse:

// ******************************************************************
// Checks if a field exists.
// ~
// Parameters:
//	1st parameter: Name of the field
//	2nd parameter: Name of the table
// Usage:
// 	LET a = $(udfFieldExists('CountryId','Countries'));
//	TRACE field exists: $(a);
// Return value:
//	Will return true (-1) if the field exists, otherwise false (0).
// ******************************************************************
SET udfFieldExists = (FieldNumber($1,$2) <> 0);

// Now call the function as follows:
LET a = $(udfFieldExists('Country','Customers'));
LET b = $(udfFieldExists('CustomerId','Customers'));
TRACE udf-result (Country): $(a);
TRACE udf-result (CustomerId): $(b);

The original german version of this article can be found here.