Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
iturner33
Partner - Contributor II
Partner - 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
Creator III
Creator III

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


View solution in original post

7 Replies
Clever_Anjos
Employee
Employee

You can build a bridge table linking Company and Users.

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

karthikoffi27se
Creator III
Creator III

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
Champion III
Champion III

What do you mean by Relate Multiple times?

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
iturner33
Partner - Contributor II
Partner - Contributor II
Author

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
Partner - Contributor II
Partner - Contributor II
Author

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

Thanks

Ian

iturner33
Partner - Contributor II
Partner - Contributor II
Author

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

Clever_Anjos
Employee
Employee

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