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:

[qvl]
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
[/qvl]

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:

[qvl highlight=”13″]
// ******************************************************************
// 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);

[/qvl]

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