Michael Ellerbeck

Find this post interesting? Do you like interesting things? Maybe you would like my invention, a connectible candle called a WickBrick!

Get one here http://wickbrick.etsy.com/

Synthetic keys occur when two or more tables have two or more fields in common.

Like if you had an ORDER_DETAILS table that had an Order ID and a ProductID and a SHIPMENTS Table that had an OrderID and a ProductID. This would cause a synthetic key.

Qlikview then creates a synthetic table. Synthetic keys are not in and of themselves errors, but they have serious performance implications. If you have a small dataset then you don’t need to worry. These take up a lot of memory because QlikView tries to store every combination of the values stored in the fields. Synthetic keys can cause inconsistency in the data structure.

If you have an app with multiple multiple synthetic keys, 7 or a dozen and some might have 4 or 5 fields, and bringing in millions of records your QlikView might not run at all. The symptom that you will see is the script editor will run all the way through, and you will get a wait cursor, and then your harddrive will start churning as qlikview tries to generate all the synthetic keys.

How to resolve, one way if you don’t have very many fields is to rename every field that you don’t want associated. Or you could comment out references in the Load statement from one of the tables.

Another way is by concatenating the two common fields together in both tables and use that to join on.

For example

Load
[Order ID] & ‘|’ & ProductID as ShipmentKey,
[Order ID],
ProductID,
[Unit Price],
Quantitay,
Discount
FROM SynKey.xls (biff, embedded labels, table is [ORDER_DETAILS]);

Directory2:
[Order ID] & ‘|’ & ProductID as ShipmentKey,
//[Order ID],
//ProductID,
[Unit Price],
Quantitay,
Discount
FROM SynKey2.xls (biff, embedded labels, table is [ORDER_DETAILS]);

A pipe ‘|’ is used to concat because it is not found anywhere in the dataset. You would then remove the duplicate fields from one table (In this case comment out the OrderID, and ProductID.

Or, if you have a lot of fields use the QUALIFY statement.

The field name will be renamed when found in a table. It will be in the form tablename.fieldname

Examples

Qualify B;
Load A,B from x.csv;
Load A,B from y.csv;
The two tables x.csv and y.csv will be joined only on A. Three fields will be
found: A, x.B, y.B.

Qualify Comment;
turns qualification on for all fields named Comment.

Qualify *;
turns qualification on for all field names.

Qualify “*ID”;
turns qualification on for all field names ending with ID.

Qualify “NR*”,A;
turns qualification on for A and all field names starting with NR.

Qualify F??;
turns qualification on for three character field names starting with F.

To link tables you need to Qualify one field. To do so use the UNQUALIFY statement for that field.

When you are done you need to use an UNQUALIFY *; to that the QUALIFY statement does not apply to other tables.

Another way is using the AutoNumber (if you had multiple, multiple fields that you needed to concat)

When you pass a unique string to the AutoNumber function it will give you back a unique integer. This creates a much more efficient key.

There is a problem!!!! If you are storing data in QVD’s external to a Qlikview Document, AUTONUMBER WILL NOT RETAIN UNIQUENESS ACROSS THE QVD’S.

In this case, new to version 8, is the Hash128 function.

This is very similar to the Autonumber function, it is not as small as the Autonumber but it WILL retain uniqueness across loading of QVD’s

Filed under: Qlikview | Tagged: Qlikview, qlikview synthetic keys | 1 Comment »