Do you Qualify?

[Jennell_McIntire]

I was recently working on a project where I had several files that I needed to load in order to determine what data I needed and what data I did not need.  I started by adding the files that I knew had data that I needed.  Then I started to add the additional files to my data model one by one.  Occasionally, I came across a file that had the same fields as a file I had already loaded.  When I ran into this, I used the Qualify statement to load the new table so that I could compare the data to the data I already loaded.  Using the Qualify statement adds the table name as a prefix to the field name.  For me this was helpful because I did not want to create synthetic tables in my data model.  I simply wanted to compare the data in the two tables to see if they were the same before I added it to my data model.

I used Qualify * to turn on the qualification of the field names.  I used the wildcard to qualify all fields loaded after the Qualify statement.

I could have also listed the fields that I wanted to Qualify instead of using the wildcard by doing something like this:

Whenever the qualify statement is used, it will qualify all of the fields loaded after the qualify statement until the end of the script is reached or until the script reaches an Unqualify statement.  The Unqualify statement turns the field qualification off.  The syntax is similar to the Qualify statement.  I can use a wildcard or a list of field names.

Once the fields are qualified, the field names will look like the image below and it will not be associated to the other tables in the data model since there are no matching field names.

Notice that CustomerAddress which is the table name is the prefix for all the fields in that table.  Now that I have this table loaded, I can take a look at the data in this table and the previously loaded customer table to determine if this is the same data set and if I need to load the CustomerAddress table at all.  It is a quick way to spot check the data before connecting it to the data model.

Now if I did not use the Qualify statement, my data model would have looked like the image below with a synthetic table because of the three field names that match (Address Number, Customer Address 1 and Zip Code) in the CustomerAddressMaster and CustomerAddress tables.  Using Qualify prevented the synthetic table from being created.

The Qualify and Unqualify statements can be used when building your data model and exploring your data sources.  The qualification of the field names eliminates matching field names therefore loading each table as an island unassociated to the other tables in the data model.  This provides an easy way to view the data sources before actually building the data model – utilizing Qlik Sense or QlikView to explore the data first. Hopefully, you will find these statements useful.

Thanks,

Jennell