How to Map a same FK twice?

[jhonatan_morais]

Hello guys, I'm in trouble to import data from a table that use a field with different names twice. (each name refers to a different business context)

Here is my entities:

tb_issues

id as issue_id  -- it is my (PK)

title

author_id as user_id  -- it is the user FK thats create the issue

assigned_to_id as user_id -- it is the user FK thats receive the issue

issue_parent_id as issue_id -- it is the (parent ) issue FK (relationship)

tb_users

id as user_id (PK)

name

i tried to map using user_id ou issue_id, but i reveived the error: Field names must be unique within table

How can i map this fields in the right way?

[swuehl · Accepted solution]

Maybe have a look at

Why You sometimes should Load a Master Table several times

with regard to the user_id.

It seems you need to handle a hierarchy for the issue_id, maybe check

Hierarchies

[swuehl]

The user_id could also be resolved using a canonical dimension user_id and a user type for the role, like shown here for a date dimension:

Canonical Date

[dwforest]

In short, instead of aliasing the user_id in tbl_issues, load the tlb_users 3 times and alias that user_id to specific id.

tb_users

id as user_id  as author_id

name as author_name

OR

If only the name is needed in the end, use a Map.

https://help.qlik.com/en-US/sense/February2018/Subsystems/Hub/Content/Scripting/ScriptPrefixes/Mappi...

[jhonatan_morais]

Nice!!! the article isn't the answer but give me a idea.

In my fisrt solution i was reloading my tbl_user several times. was working bum i was unhappy with because the data was duplicated in many entities.

After read the article i split the entities in other specialized entitites.

here is my new structure:

tb_issues

id as issue_id  -- it is my (PK)

title

tb_users

id as user_id (PK)

name

tb_issues_authors

issue_id

author_id

tb_issues_parent

issue_parent_id

issue_id

tb_issues_assign

assigned_to_id

issue_id

OK, Now I get more entities, but my data isn't duplicate.

Thanks for your help.

PS: if you know more good articles links, fell free to share with me. Thanks a lot.

[jhonatan_morais]

Thanks David. help me a lot.

[swuehl]

I can recommend all articles of the Qlik design blog,

Qlik Design Blog

Especially the ones written by Henric Cronstroem.

There are also very good blogs by developers:

https://www.askqv.com/blogs/