Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

iturner33
New Contributor II

Using a related table multiple times in one table ?

Hi, I have a requirement for a company table to relate to user table multiple times, ie Creator, Last Edited By and Account Owner - not sure how to represent this in the dataload editor without loading the user table in 3 times, which seems a little dumb.

So I have (simplified form):

[Company]:

LOAD

     company_name,

     created_by,

     updated_by,

     record_owner

FROM [lib://Core Data/companies.xlsx]

(ooxml, embedded labels, table is companies);

[User]:

LOAD

     user_id,

     fullname,

     role,

     email_address,

     active

FROM [lib://Core Data/users.xlsx]

(ooxml, embedded labels, table is users);


I need all of the fields marked in blue to relate to the [User] Table so I can pull out the updated_by Role or the record_owners active status etc..


Thanks

Ian

1 Solution

Accepted Solutions
karthikoffi27se
Contributor III

Re: Using a related table multiple times in one table ?

Hi Ian


You can bring all the user information to company table using mapping load and apply map function.

In that way you can segregate creator, updater and record owner information.


Map_Creator_Name:


Mapping Load

   user_id,

   fullname

FROM [lib://Core Data/users.xlsx]

(ooxml, embedded labels, table is users);


Map_Creator_Role:


Mapping Load

   user_id,

   role

FROM [lib://Core Data/users.xlsx]

(ooxml, embedded labels, table is users);


Map_Creator_Email:


Mapping Load

   user_id,

   email_address

FROM [lib://Core Data/users.xlsx]

(ooxml, embedded labels, table is users);


Map_Creator_ActiveStatus:


Mapping Load

   user_id,

   active

FROM [lib://Core Data/users.xlsx]

(ooxml, embedded labels, table is users);


[Company]:

LOAD

     company_name,

     created_by,

Applymap('Map_Creator_Name',created_by,'Not Defined') as Creator_Name,

Applymap('Map_Creator_Role',created_by,'Not Defined') as Creator_Role,

Applymap('Map_Creator_Email',created_by,'Not Defined') as Creator_Email,

Applymap('Map_Creator_ActiveStatus',created_by,'Not Defined') as Creator_Status,

     updated_by,

     record_owner 

FROM [lib://Core Data/companies.xlsx]

(ooxml, embedded labels, table is companies);

 

Please do the same for Updater and record owner too.


Hope this helps.


Many Thanks

Karthik


7 Replies
Employee
Employee

Re: Using a related table multiple times in one table ?

You can build a bridge table linking Company and Users.

Please share a piece of your files, I would help you building that

karthikoffi27se
Contributor III

Re: Using a related table multiple times in one table ?

Hi Ian


You can bring all the user information to company table using mapping load and apply map function.

In that way you can segregate creator, updater and record owner information.


Map_Creator_Name:


Mapping Load

   user_id,

   fullname

FROM [lib://Core Data/users.xlsx]

(ooxml, embedded labels, table is users);


Map_Creator_Role:


Mapping Load

   user_id,

   role

FROM [lib://Core Data/users.xlsx]

(ooxml, embedded labels, table is users);


Map_Creator_Email:


Mapping Load

   user_id,

   email_address

FROM [lib://Core Data/users.xlsx]

(ooxml, embedded labels, table is users);


Map_Creator_ActiveStatus:


Mapping Load

   user_id,

   active

FROM [lib://Core Data/users.xlsx]

(ooxml, embedded labels, table is users);


[Company]:

LOAD

     company_name,

     created_by,

Applymap('Map_Creator_Name',created_by,'Not Defined') as Creator_Name,

Applymap('Map_Creator_Role',created_by,'Not Defined') as Creator_Role,

Applymap('Map_Creator_Email',created_by,'Not Defined') as Creator_Email,

Applymap('Map_Creator_ActiveStatus',created_by,'Not Defined') as Creator_Status,

     updated_by,

     record_owner 

FROM [lib://Core Data/companies.xlsx]

(ooxml, embedded labels, table is companies);

 

Please do the same for Updater and record owner too.


Hope this helps.


Many Thanks

Karthik


vinieme12
Esteemed Contributor II

Re: Using a related table multiple times in one table ?

What do you mean by Relate Multiple times?

can you post a sample on how the final result should look like?

iturner33
New Contributor II

Re: Using a related table multiple times in one table ?

Not sure what you mean by a bridge table.    I cant share the data is its not my data and the files are very large. 

iturner33
New Contributor II

Re: Using a related table multiple times in one table ?

That looks very interesting - I will give it a try & let you know how it goes.

Thanks

Ian

iturner33
New Contributor II

Re: Using a related table multiple times in one table ?

Brilliant, works well - thanks - just what I was looking for.

Do you know if this is efficient on memory and will work when I scale to 100's of thousands of records ?

Ian

Employee
Employee

Re: Using a related table multiple times in one table ?

Please check this

[Companies]:

LOAD

  [company_name],

  [created_by],

  [updated_by],

  [record_owner]

FROM [lib://tmp/Pasta1.xlsx]

(ooxml, embedded labels, table is Companies);

[users]:

LOAD

  [user_id],

  [fullname],

  [role],

  [email_address],

  [active]

FROM [lib://tmp/Pasta1.xlsx]

(ooxml, embedded labels, table is users);

bridge:

LOAD DISTINCT

  [created_by],

    [created_by] as [user_id]

resident Companies;

join

LOAD DISTINCT

  [updated_by],

    [updated_by] as [user_id]

resident Companies;

join

LOAD DISTINCT

  [record_owner],

    [record_owner] as [user_id]

resident Companies;

Capturar.PNG

Community Browser