Do you Qualify?- How to use QUALIFY statement

[robert_mika]

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:

Product	Payment

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

Remember

• 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:

Table Viewer:

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:

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:

Table Viewer:

Feeling Qlikngry?

How To /Missing Manual(18 articles)

[richard]

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]

Thank you Colin for the additional information.

[stevedark]

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]

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]

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]

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]

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

[Anonymous]

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

[robert_mika]

Thank you for your comments.

Is not much about renaming is more about prefixing.

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