Qlik Community

QlikView Documents

Documents for QlikView related information.

Do you Qualify?- How to use QUALIFY statement

Do you Qualify?- How to use QUALIFY statement


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)

Attachments
Comments
Partner
Partner

Good basics!

0 Likes

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

Thank you Colin for the additional information.

0 Likes
MVP & Luminary
MVP & Luminary

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

Luminary
Luminary

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".

Luminary
Luminary

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

MVP & Luminary
MVP & Luminary

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.

pandunallani
Contributor II

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

0 Likes
amuratbar
New Contributor III

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

0 Likes

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
Revision #:
1 of 1
Last update:
‎2015-02-17 05:15 PM
Updated by: