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?

[mrooney]

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.

[evan_kurowski]

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:

1

2

3

[engishfaque]

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

[#5]

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?

[evan_kurowski]

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.

[#7]

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)

[MarcoWedel]

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

[MarcoWedel]

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

[#10]

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

[MarcoWedel]

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