Using a related table multiple times in one table ?

[iturner33]

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

[Clever_Anjos]

You can build a bridge table linking Company and Users.

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

[karthikoffi27se · Accepted solution]

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]

What do you mean by Relate Multiple times?

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

[iturner33]

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]

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

Thanks

Ian

[iturner33]

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]

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;