Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report

Do you Qualify?- How to use QUALIFY statement

cancel
Showing results for 
Search instead for 
Did you mean: 
robert_mika
Master III
Master III

Do you Qualify?- How to use QUALIFY statement

Last Update:

Feb 17, 2015 5:15:46 PM

Updated By:

robert_mika

Created date:

Feb 17, 2015 5:15:46 PM

Attachments


What it is used for?

The Qualify statement is used for switching on the qualification of field names, i.e. field names will get the table name as a prefix.’

In situation where we have two (or more) Tables with the same field names:

ProductPayment
product.jpgpayment.jpg

The Qualify statement will assign name of the Table to fields:

Remember ligtbulb.jpg

  • To use the script below the Excel file must be saved in the same folder that your qvd file

        Otherwise you will need to make changes the this path - [Qualify.xlsx]

QUALIFY *;
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(
ooxml, embedded labels, table is Product);

QUALIFY *;
Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(
ooxml, embedded labels, table is Payment);

The Outcome:

6.jpg

Table Viewer:

TV1.jpg

Read only if you need to know more....


If we have not used ‘QUALIFY’ statement in situation as above Qlikview would map both of the Tables with outcome like below:

 

TV2.jpg

The end result  -merge of those two Tables is correct only for the “Serial No” fields

The “Value” and “Category” fields although merged is none of use to us.

To fix this we can only Qualify fields that we do not want to associated:

QUALIFY Category,
Value;
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(
ooxml, embedded labels, table is Product);

QUALIFY Category,
Value;
Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(
ooxml, embedded labels, table is Payment);


or by using UNQUALIFY statement:

(which works as opposite to QUALIFY)

QUALIFY *;
UNQUALIFY [Serial No];
Product:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(
ooxml, embedded labels, table is Product);

QUALIFY *;
UNQUALIFY [Serial No];

Payment:
LOAD [Serial No],
Category,
Value
FROM
Qualify.xlsx
(
ooxml, embedded labels, table is Payment);

In second example the ‘QUALIFY *’ part will add Table name to all fields and the UNQUALIFY statement will remove those names only from specified fields(this method is very useful if we got large numbers of fields)

Outcome is as below:

Fields:

TV2.jpg

Table Viewer:

tv3.jpg

Feeling Qlikngry?

How To /Missing Manual(18 articles)

Comments
richard
Partner - Creator
Partner - Creator

Good basics!

Colin-Albert

Hi Robert,

A good post explaining Qualify in simple terms.

It may be worth mentioning that you can use wildcards in the unqualify command.

The script below will unqualify any fields where the name starts with %_

        

    Qualify * ;

    Unqualify "%_*" ;

    Payment:
     LOAD
[Serial No] as %_SerialNo,
          Category,
          Value
     FROM
      Qualify.xlsx
      (
ooxml, embedded labels, table is Payment);

I also recommend that each tab in the load script where qualify is used, should end with an Unqualify * statement. so qualify is switched off before the next tab is executed.
If the next tab needs qualify, then add the required statements at the start of the tab.

Using this method avoids any problems if the tab order is changed using promote & demote causing unexpected data to be qualified or not.

Regards

Colin

robert_mika
Master III
Master III

Thank you Colin for the additional information.

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

I would add that it is a good idea never to use QUALIFY in a production application.  It's a handy little tool for rapid prototyping (as you can avoid unintentional associations), but it leaves you with ugly field names.  Part of what you should be doing in your load is renaming fields so that they are human readable - and having a table prefix does not do this.

Rename each field independently and sensibly and your life will be greatly improved.  During this process you can remove unwanted associations, with a field name that denotes where it came from even.  But you have to admit, as a column heading:

Project Description

Looks better than:

Project.ProjectDescription


Good article on the use of QUALIFY none the less...

Steve

juraj_misina
Luminary Alumni
Luminary Alumni

I've seen quite an interesting use of QUALIFY to create fieldnames which could be easily managed by front end switches. The goal was to measure similar metrics for various entities, so the developer - using QUALIFY - created fieldnames like BOX.QUANTITY, BOX.UNIT, BOX.VOLUME and CONTAINER.QUANTITY, CONTAINER.UNIT, CONTAINER.VOLUME, etc. Then it was very easy to create a set of switches to control what's displayed in charts.

But, apart from specific uses like this, I agree with stevedark: field names should be "nice" whenever possible. Plus, be very careful, you can easily get lost if using QUALIFY in more complex scripts. I spent some hours wondering (and wandering through scripts) why the heck the "field cannot be found".

jvitantonio
Luminary Alumni
Luminary Alumni

Hi,

I don't see why you shouldn't use this in a production environment when you can use the Rename Fields function at the end of your script.

Either way I do agree we should make the field names readable and clear to the user.

JV

stevedark
Partner Ambassador/MVP
Partner Ambassador/MVP

If you are going to issue a RENAME statement, why not just do an AS statement when you read the field - removing the need for a QUALIFY?

I suppose that as long as the field name you show to users is sensible it doesn't entirely matter how you get there.

Anonymous
Not applicable

Nice document, it explained how can qualify work on table and each column.

Anonymous
Not applicable

Very helpfull document, thank you. it is simple way but i prefer rename fields by myself

robert_mika
Master III
Master III

Thank you for your comments.

Is not much about renaming is more about prefixing.

Changing 100 rows one by one could be tiresome...

Version history
Last update:
‎2015-02-17 05:15 PM
Updated by: