topic Re: Help with synthetic keys in QlikView

Help with synthetic keys

Fri, 29 Aug 2014 14:14:17 GMT

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?

Re: Help with synthetic keys

giakoum — Fri, 29 Aug 2014 14:16:02 GMT

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

Re: Help with synthetic keys

PrashantSangle — Fri, 29 Aug 2014 14:24:57 GMT

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,

Re: Help with synthetic keys

JonnyPoole — Fri, 29 Aug 2014 14:30:01 GMT

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

Re: Help with synthetic keys

jagan — Fri, 29 Aug 2014 14:45:12 GMT

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.

Re: Help with synthetic keys

simondachstr — Fri, 29 Aug 2014 14:48:25 GMT

Check out this blog-post:

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

Re: Help with synthetic keys

walterlh — Fri, 29 Aug 2014 14:49:21 GMT

Con autonumber

Re: Help with synthetic keys

MarcoWedel — Fri, 29 Aug 2014 16:54:24 GMT

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

Re: Help with synthetic keys

evan_kurowski — Fri, 29 Aug 2014 20:35:03 GMT

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:

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.

Re: Help with synthetic keys

Fri, 29 Aug 2014 21:19:12 GMT

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

Re: Help with synthetic keys

mrooney — Fri, 29 Aug 2014 21:40:33 GMT

Yes its is possible, but only in case you make a simple concatenation.

After that, you can use right(), mid() , left() nad/or subfield() to show the portion of the concatenated key that you want.

Re: Help with synthetic keys

evan_kurowski — Fri, 29 Aug 2014 21:55:29 GMT

In the event you decided to drop all individual fields forming subcomponents of a compound key, if you do not use autonumber(), the compound key will still display the original values.

If you need to display the individual components as stand alone fields in the data model, you either have to perform aliasing or qualify the synthetic components, or leave them behind and "re-breakout" the subcomponents (and there are arguments that the syn-key does not impact performance, only design complexity, and it is ok to leave it in the app).

If your Key were formed 'JAN_APPLE_BLUE_EAST'

=Subfield(Key,'_',2) will produce the value in the 2nd component of the compound key, without having to carry it as its own field in the data model.

Do not skip using a delimiter in the compound key if you intend to display it on the UI unless you are certain the values won't overlap. Autonumber will be able to assign these 3 rows distinct values, but a non-delimited concatenation of these fields will not.:

COMPONENT1 COMPONENT2 COMPONENT3

AB CDEF GHI

ABC DEFG HI

A BC DEFGHI

Skipping delimiters will combine the 3 distinct rows above to form 1 compound key value :

'ABCDEFGHI'

if you use a delimiter you will get 3 distinct compound key values:

'AB_CDEF_GHI'

'ABC_DEFG_HI'

'A_BC_DEFGHI'

With or without delimiters Autonumber(COMPONENT1, COMPONENT2, COMPONENT3) will produce:

Re: Help with synthetic keys

engishfaque — Sat, 30 Aug 2014 05:42:45 GMT

Dear qlikview_beginner,

This way you can remove the Synthetic Key.

Example your Data Model consist on synthetic key. Then using Hash128 create ALIAS .

Hash128(BrokerID, BrokerName, BondID, BondName, BondAddress) AS ActualID

Create New Table "temp1" and use Resident Load for actual table1.

Again Create New Table "temp2" and Join Previous Table "temp1" as well as use Resident Load for previous table2.

Finally, DROP Fields BrokerID, BrokerName, BondID, BondName, BondAddress From table1;

Finally, DROP Fields BrokerID, BrokerName, BondID, BondName, BondAddress From table2;

Kind regares,

Ishfaque Ahmed

Re: Help with synthetic keys

Tue, 02 Sep 2014 13:55:03 GMT

Thank you all for your help. I'm still having trouble following because I'm so new to this...I need the ability to display each field in a chart. Does anyone have an example app that shows this?

Re: Help with synthetic keys

evan_kurowski — Tue, 02 Sep 2014 16:40:12 GMT

Hello QlikView_beginner,

Maybe you should post some specific images of what you'd like to accomplish, or attach a .QVW that you are stuck on. The "ability to display each field in a chart" could mean a variety of things. I think if you narrow down your issue with some attached examples, we'll get to a workable solution.

Re: Re: Help with synthetic keys

Thu, 11 Sep 2014 19:57:15 GMT

Hi Evan,

Sorry for the delay. Here is a small example QVW and data file of what it looks like...... the goal is to model it in a way that there are no remaining synthetic keys. In this example, I only have 1 synthetic key but I have about 4 in my report.

What is a good way to model this data when there are more than 1 key field in a table? I would need to be able to see the key fields in a the straight chart as well shown in the QVW..(i.e week, product, location, etc)

Re: Re: Re: Help with synthetic keys

MarcoWedel — Thu, 11 Sep 2014 20:36:52 GMT

Hi,

one possible solution for this data:

facts:
LOAD version,
    week,
    product,
    location,
    orders
FROM
[synthetic key help.xls]
(biff, embedded labels, table is orders$);

Join
LOAD version,
    week,
    product,
    location,
    [actual production]
FROM
[synthetic key help.xls]
(biff, embedded labels, table is production$);

Join
LOAD version,
    week,
    product,
    location,
    inventory
FROM
[synthetic key help.xls]
(biff, embedded labels, table is inventory$);

Join
LOAD version,
    week,
    product,
    location,
    forecast
FROM
[synthetic key help.xls]
(biff, embedded labels, table is forecast$);

hope this helps

regards

Marco

Re: Re: Re: Re: Help with synthetic keys

MarcoWedel — Thu, 11 Sep 2014 21:21:39 GMT

or maybe:

LOAD version,
    week,
    product,
    location,
    orders,
    AutoNumberHash128(version, week, product, location) as %ID
FROM
[synthetic key help.xls]
(biff, embedded labels, table is orders$);

LOAD version,
    week,
    product,
    location,
    [actual production],
    AutoNumberHash128(version, week, product, location) as %ID
FROM
[synthetic key help.xls]
(biff, embedded labels, table is production$);

LOAD version,
    week,
    product,
    location,
    inventory,
    AutoNumberHash128(version, week, product, location) as %ID
FROM
[synthetic key help.xls]
(biff, embedded labels, table is inventory$);

LOAD version,
    week,
    product,
    location,
    forecast,
    AutoNumberHash128(version, week, product, location) as %ID
FROM
[synthetic key help.xls]
(biff, embedded labels, table is forecast$);

tabLink:
LOAD Distinct
    version, week, product, location, %ID
Resident orders$;

LOAD Distinct
    version, week, product, location, %ID
Resident production$
Where not Exists(%ID);

LOAD Distinct
    version, week, product, location, %ID
Resident inventory$
Where not Exists(%ID);

LOAD Distinct
    version, week, product, location, %ID
Resident forecast$
Where not Exists(%ID);

DROP Fields version, week, product, location From orders$, production$, inventory$, forecast$;

hope this helps

regards

Marco

Re: Help with synthetic keys

Thu, 11 Sep 2014 21:46:45 GMT

Are there any benefits to doing it the first way vs the second, vice versa?

Re: Re: Re: Re: Help with synthetic keys

MarcoWedel — Thu, 11 Sep 2014 21:55:37 GMT

There is a fine description by Henric Cronström that describes generic tables, a structure similar to your's:

http://community.qlik.com/blogs/qlikviewdesignblog/2014/03/31/generic

Bottom line: keeping seperate tables with manually created combined key (second way) might be the better choice.

hope this helps

regards

Marco