topic How to avoid a join condition in QlikView

How to avoid a join condition

Fri, 13 Jan 2012 20:37:03 GMT

Assuming you have the following simple data set

Customer Table :

CUSTOMER_ID

ADDRESS_ID

Address Table :

ADDRESS_ID

ZIP_CODE

Is there anyway, apart from using an Alias in the script, or a column alias using AS to avoid QV joining the two tables by the ID ? I would like to ensure that the associative join is always via ADDRESS_ID, and that no surrogate key gets created for the ID -> ID.

One solution is to script the Address table ID AS ADDR_ID for example, but it would be nice if script syntax allowed an equivalent of a SQL WHERE ADDRESS_ID = ADDRESS_ID.

Regards

Andrew.

How to avoid a join condition

Miguel_Angel_Baeyens — Fri, 13 Jan 2012 23:23:25 GMT

Hi Andrew,

I'd go one step further, that is, since in QlikView you have all records linked, as you say, by the name of the field, and only in case ID is the same in both tables, you only need the ID field in one of either tables! You will select a value in ADDRESS_ID and you will get the values in ID, regardless the table this field is into. And why having twice the same data?

Well, yes, it may be the case that in fact both ID fields are named alike but they store different information. In that case, I'd better have them "separated" not just because QlikView will use them to link two tables and that might return unexpected results, but because they are just different.

You cannot prevent QlikView from linking using all fields named the same in different tables, or say "use this field but not this one" if they share names in different tables, and that's one of the powers of this tool, when understod and used properly.

Hope that makes sense.

Miguel

How to avoid a join condition

Mon, 16 Jan 2012 10:12:58 GMT

Sorry - I should have explained that I am working with a vendor data model. The Id field present in both tables in fact has different values. I've found the solution - see post below - but thanks for your advice.

Qualify

The automatic join between fields with the same name in different tables can be suspended by means of the qualify statement, which qualifies the field name with its table name. If qualified, the field name(s) will be renamed when found in a table. The new name will be in the form of tablename.fieldname. Tablename is equivalent to the label of the current table, or, if no label exists, to the name appearing after from in load and select statements.

Qualification is always turned off by default at the beginning of script execution. Qualification of a field name can be activated at any time using a qualify statement. Qualification can be turned off at any time using an Unqualify statement.

The syntax is:

qualify*fieldlist

*fieldlist is a comma separated list of the fields for which qualification should be turned on. Using * as field list indicates all fields. The wildcard characters * and ? are allowed in field names. Quoting of field names may be necessary when wildcards are used.

Note!
The qualify statement should not be used in conjunction with partial reload!

Examples:

Qualify B;

Load A,B from x.csv;

Load A,B from y.csv;

The two tables x.csv and y.csv are joined only on A. Three fields will result: A, x.B, y.B.

In an unfamiliar database, start out by making sure that only one or a few fields are associated, as illustrated in this example:

qualify *;

unqualify TransID;

select * from tab1;

select * from tab2;

select * from tab3;

Only TransID will be used for associations between the tables tab1, tab2 and tab3.