Skip to main content
Jennell_McIntire
Employee

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.

script full.png

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

script full 2.png

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.

unassoc.png

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.

data model.png

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

4 Comments
stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Jennell,

Thanks for the post.  As you say the QUALIFY statement can be useful - sometimes.  I have however seen it abused far too many times.  Developers using it as a quick 'get out of jail' card, and an excuse to not work on a decent data model design.

To my mind, all field names should be friendly and look nice by the time they appear in the data model.  This way when a user comes to pick them for use in a chart they are as they want them displayed.  This is especially true with the repositories in Sense.  It's also important in QlikView, especially as you can not alias field names in the GetCurrentField function for Cycle groups.

I would go as far to say that whilst QUALIFY can be useful for rapid prototyping of data sources (as you describe) it should never find it's way into a production application.

Just my humble opinion though...

Steve

894 Views
amonjaras
Partner - Creator II
Partner - Creator II

Hello!

Thank you Jennell and Steve.

I consider QUALIFY an effective "quick and dirty" tool for when you need to BE quick and dirty; that is, on a SIB or proof of concept scenario, if you don't have too much time to come up with a proper data model.

Angel.

894 Views
montubhardwaj
Specialist
Specialist

Thanks much for wonderful article. But I agree with Steve here.

894 Views
Not applicable

Thank you for posting good article

0 Likes
894 Views