Help with synthetic keys

[#1]

I have four facts tables with measures in it. However, I also have four key fields in each of those tables that I need but it creates synthetic keys and affects the performance. How can I fix this?

[giakoum]

create one key combining all 4 keys, and use the new combined key in the other 3 tables.

that means drop the fields from them and only keep the combined key

[PrashantSangle]

Hi,

You can achieve this by creating a common Key field by joining all those field.

And then Rename Those field if you want use those field in further calculation.

You can Combine those field like

field1&'-'&field2&'-'&field3&'-'&field4 as keyField

Regards,

[JonnyPoole]

An alternative is to use the autonumber to create an integer based key that takes up less room than a long string for example:

autonumber(  field1&field2&field3&field4, 'Key1') as Key

The other table you can reference different field names that mean the same thing, just use the same identifier 'Key1' as the 2nd parameter and to associate on the key alias the field with the same name...Key.

autonumber(  field5&field6&field7&field8, 'Key1') as Key

[jagan]

Hi,

Try link table concept or create keys to deal this.

Creating Key:

Table1:

LOAD

Key1 & Key2 & Key3 & Key4 AS Key,

'

'

FROM DataSource1;

Table2:

LOAD

Key1 & Key2 & Key3 & Key4 AS Key,

'

'

FROM DataSource2;

'

'

'

Hope this helps you.

If you need Key fields to display as filters or in dimension then try linked tables, check in community there are lot of posts regarding this.

Regards,

Jagan.

[simondachstr]

Check out this blog-post:

Qlik Tips: Rules for creating a Key/Link Table in QlikView

[walterlh]

Con autonumber

[MarcoWedel]

AutonumberHash128(key1, key2, key3, key4) as CombinedKey

[evan_kurowski]

I agree with the suggestions to AutoNumber() compound keys.

If I could add to that suggestion, I wouldn't add Autonumber until the very last development pass of the application.  The autonumber makes it harder to "decipher" and track which actual values are being combined.

A "healthy" 4 field compound key delimited by underscore might look something like this:
'JAN_4503_A43_201204V'

But if something were somehow going awry in the logic sequences of the compound key formation, autonumber makes it harder to discern, because a "malformed" compound key gets a valid autonumber just the same way that a "healthy" one does.  For example:

[Compound_Keys]:

'__B22_201104Z'          (broken, missing subfield 1 & 2)

'JUL___201406B'         (broken, missing subfield 2 & 3)

'____'                             (broken, missing all subfields)

'JAN_5444_B45_201105Z'     (healthy)

'_A98__'                        (broken, missing subfield 1, 3, & 4)

// Only 1 of the keys above is "healthy" but autonumber will produce:

1

2

3

4
5

The same thing applies if you are using Autonumber() against unevenly populated fields:

[Region], [Country], [City]

EU, GE, BE

EU, GB, LO

EU, , RO

EU
AP,,TO

In this scenario, Autonumber(Region,Country,City), all data for the EU that is missing the 2nd and 3rd components will bunch up under autonumber = 4, but now in the user interface the Region tallies will not reconcile against the Country and City tallies because an uneven key is present.  (so if someone in Scandinavia and someone in Spain both forget to enter Country and City, but remembered to enter Region when entering data in this example, both their entries would bunch up together under just 'EU'.  Maybe this is fine, or maybe this is something you want to flag as a data-quality issue.)

Looking at the "longhand" version of the compound keys can be a useful indicator of data model health, and can show at a glance which values are broken.  Once these are converted to autonumber values there's no way to "decode" the key just by looking.  If you have an app that isn't impacted by an uneven keying system, no worries, otherwise being able to see the values in the key is very helpful.

[#10]

I need the ability to display those keys though.... so is this still a possibility?