Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Help with synthetic keys

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?

21 Replies
giakoum
Partner - Master II
Partner - Master II

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
JonnyPoole
Employee
Employee

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
Luminary Alumni
Luminary Alumni

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
Luminary Alumni
Luminary Alumni

walterlh
Creator II
Creator II

Con autonumber

MarcoWedel

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

evan_kurowski
Specialist
Specialist

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.

Not applicable
Author

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