article Do you Qualify?- How to use QUALIFY statement in Member Articles

Do you Qualify?- How to use QUALIFY statement

robert_mika — Tue, 17 Feb 2015 22:15:46 GMT

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)

Re: Do you Qualify?- How to use QUALIFY statement

richard — Thu, 19 Feb 2015 10:27:23 GMT

Good basics!

Re: Do you Qualify?- How to use QUALIFY statement

Colin-Albert — Thu, 19 Feb 2015 19:02:44 GMT

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

Re: Do you Qualify?- How to use QUALIFY statement

robert_mika — Thu, 19 Feb 2015 19:07:32 GMT

Thank you Colin for the additional information.

Re: Do you Qualify?- How to use QUALIFY statement

stevedark — Fri, 20 Feb 2015 19:51:02 GMT

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

Re: Do you Qualify?- How to use QUALIFY statement

juraj_misina — Mon, 23 Feb 2015 08:43:19 GMT

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

Re: Do you Qualify?- How to use QUALIFY statement

jvitantonio — Tue, 24 Feb 2015 16:44:39 GMT

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.

Re: Do you Qualify?- How to use QUALIFY statement

stevedark — Tue, 24 Feb 2015 23:33:09 GMT

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.

Re: Do you Qualify?- How to use QUALIFY statement

Anonymous — Sat, 08 Oct 2016 02:40:56 GMT

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

Re: Do you Qualify?- How to use QUALIFY statement

Anonymous — Fri, 10 Mar 2017 08:03:32 GMT

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

Re: Do you Qualify?- How to use QUALIFY statement

robert_mika — Fri, 10 Mar 2017 13:20:16 GMT

Thank you for your comments.

Is not much about renaming is more about prefixing.

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

Re: Do you Qualify?- How to use QUALIFY statement

Sun, 16 Apr 2017 11:51:26 GMT

Hi Robert,

Very useful. Looks like the excel file is missing Value '4' showing in the qvw. If so, I can't get my head around as to how it is being loaded in the qvw?

Thanks in advance

Re: Do you Qualify?- How to use QUALIFY statement

Tanalex — Fri, 28 Apr 2017 18:51:51 GMT

Simple, informative article. Extremely helpful. Thanks robert_mika.

Also, good point by colin_albertto use UNQUALIFY * at the end of an script.

Re: Do you Qualify?- How to use QUALIFY statement

Anonymous — Thu, 22 Jun 2017 09:03:23 GMT

very useful

Re: Do you Qualify?- How to use QUALIFY statement

Sat, 08 Jul 2017 08:13:22 GMT

Very usefull Robert.

Re: Do you Qualify?- How to use QUALIFY statement

passionate — Mon, 10 Jul 2017 16:35:26 GMT

Good and Simplified

Re: Do you Qualify?- How to use QUALIFY statement

robert_mika — Mon, 10 Jul 2017 20:54:51 GMT

Thank you

Re: Do you Qualify?- How to use QUALIFY statement

robert_mika — Mon, 10 Jul 2017 20:55:00 GMT

Thank you

Re: Do you Qualify?- How to use QUALIFY statement

Anonymous — Tue, 31 Oct 2017 09:12:41 GMT

Very useful document.

Thanks a lot.

Re: Do you Qualify?- How to use QUALIFY statement

Kieli — Mon, 25 Nov 2019 13:00:14 GMT

Hi guys,

thanks for the post.

Unfortunately, I have a problem with the Qualify statement: whenever I qualify a certain table it is not possible any longer to automatically refresh it. It turns both documents to a "script" and both are no longer connected to my QlikServer. Do you know why?

Thanks for the help!
Elisabeth