topic Re: Problem with Synthetic key in QlikView

Problem with Synthetic key

Anonymous — Wed, 18 Dec 2013 13:24:13 GMT

Hi,

in a data model there is a synthetic key. Do you have any idea to eliminate this bug?

Regards

Stefan

Re: Problem with Synthetic key

Wed, 18 Dec 2013 13:34:28 GMT

Difficult without understanding your data and the relationships between your tables but try concatenating the first two, i.e.

Mon1:

LOAD date,

text(month(date)) as mon,

Date(date, 'YYYY') as fy,

sub as sb,

FROM

D:\Projekte\QlikView\mon.xlsx

(ooxml, embedded labels);

Concatenate(Mon1)

LOAD date,

text(month(date)) as mon,

Date(date, 'YYYY') as fy,

sub as sb,

FROM

D:\Projekte\QlikView\mon.xlsx

(ooxml, embedded labels, table is Tabelle2);

Mon3:

LOAD sub as sb,

name

FROM

D:\Projekte\QlikView\mon.xlsx

(ooxml, embedded labels, table is Tabelle3);

MonthQuarter:

Noconcatenate Load * Inline [

Month1, MonthName, Quarter, mon

1, January, 2, Jan,

2, February, 2, Feb,

3, March, 2, Mrz,

4, April, 3, Apr,

5, May, 3, May,

6, June, 3, Jun,

7, July, 4, Jul,

8, August, 4, Aug,

9, September, 4, Sep,

10, October, 1, Oct,

11, November, 1, Nov,

12, December, 1, Dec]

Re: Problem with Synthetic key

datanibbler — Wed, 18 Dec 2013 13:38:07 GMT

Hi Stefan,

a synthetic key is not a but - but it can be annoying all right.

That is because of QlikView's built-in function of automatically linking tables based on (ALL) the fields that have the same name - which can sometimes even break everything when the content of the fields does not match, only the name is the same ...

You have several options:

- Rename fields so that only the one field you want to link will be automatically linked by QlikView

- use a link_table (that is actually quite the same as a synthetic key, just "in goody")

- join the two tables if that can be done.

HTH

Best regards,

DataNibbler