Monday 13 February 2012

Qualifying Field Names

The QUALIFY statement adds the table name to the front of the specified field(s) separated by a full stop (period for our American friends) and applies to fields loaded after the qualify statement only. More than one field can be named in the same QUALIFY statement as follows:

QUALIFY Name, StartDate, EndDate;

QUALIFY can be very useful for ensuring field names are unique and thus avoiding relationships between tables that aren't intentional. For example, too many times have I come across a field called "Name" in multiple tables in a database which when loaded into QlikView results in data model issues such as customers being connected to employees by their name.

As you might expect, QUALIFY has a sister statement UNQUALIFY which is used to eliminate fields from a previous QUALIFY statement. But why would you need to do this rather than simply removing the field name from the QUALIFY statement? Well the key lies with the ability to use wild cards in a QUALIFY statement like this:

QUALIFY *;
UNQUALIFY EmployeeID, OfficeID;


The down side to qualify is that it can result in very long, and some times meaningless field names and so I do not use it in production solutions, preferring to rename fields that cause problems to something more meaningful and unique. I have however found one really powerful use for QUALIFY. When performing "Seeing Is Believing" (SIB) events for prospective clients or when prototyping a new application, time is at a premium and so speed is everything. SIBs can be high pressure environments and you are normally loading in tables from a source you're not familiar with and relying on the client to tell you how the data is structured. In this scenario being able to ensure all field names are unique, except those you explicitly specify as key fields, can save a lot of time and embarrassment. The fastest method I've found is to QUALIFY all fields and then UNQUALIFY all fields which start with a prefix such as "Key_". You can do this as follows:

QUALIFY *;
UNQUALIFY "Key_*";

Employees:
LOAD
   EmployeeID AS Key_EmployeeID,
   Name,
   OfficeID AS Key_OfficeID,
   StartDate,
   EndDate
FROM employees.qvd (qvd);


Noted the double quotes in the UNQUALIFY statement, as these are importand. I hope this little technique saves you as many headaches as it has me over the years I've worked with QlikView.

No comments:

Post a Comment