topic Re: Issue with synthetic keys in QlikView

Issue with synthetic keys

Sun, 09 Nov 2014 13:01:34 GMT

Hello,

I have more than 3 tables which include fields with the same name.

I want to make sure they are shown in the Qlikview with the right connections and not with synthetic keys.

Anyone can please assist how to resolve that?

posts:

post_id (PK)

user_id (FK)

message_id (FK)

message_text

tag_id (FK)

tags:

messages:

message_id (PK)

user_id (FK)

comments:

comment_id (PK)

user_id (FK)

post_id (PK)

comment_text

users:

user_id (PK)

user_name

organizations:

organization_id (PK)

organization_name

roles:

role_id (PK)

organization_id (FK)

user_id (FK)

is_active

Re: Issue with synthetic keys

robert99 — Sun, 09 Nov 2014 14:04:34 GMT

Read this

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

and 5 tables include the same name (userID)

And only 2 include the same name twice userID & messageID AND userID & postID

As far as I can see. You could join them if you want but userID & messageID AS usermessageID. But then you woudl have to rename messageID and postID or leave them out from the load.

But Im confused by what your tables include TBH. What does the table users include apart from the userID

Re: Issue with synthetic keys

Anonymous — Sun, 09 Nov 2014 14:24:49 GMT

organizations: , roles: and users: are your dimensions, I'd applymap() or join them into a single users: table.

posts: , messages: and comments: , are your facts so applymap() or join them into a single facts: table.

[I am not sure whether tags: is a dimension, fact or a typo in your post.]

Re: Issue with synthetic keys

Sun, 09 Nov 2014 16:35:01 GMT

There are more fields. I just added here the PM and FK fields in order to prevent any confusion.

I edited again. Hope it's more clear.

Re: Issue with synthetic keys

Mon, 10 Nov 2014 05:31:52 GMT

Hi Nimrod,

If I understand you correctly,

Please try to see the attached file.

Re: Issue with synthetic keys

sujeetsingh — Mon, 10 Nov 2014 05:34:13 GMT

Moran,

First you need to analyse what you want from the all 5 tables.

You need to draw a diagram of data model and the valid key to join them .