Connecting up two different tables to a third table using the same key

[james_hanify]

Hi all,

The structure of our data is something like

Account can have a salesperson

Invoice can have a salesperson

Salesperson has an id which is linked via account and invoice, and that id has a name next to it, what I would rather not do is do an apply map as there is additional data as part of this table and I believe applymap only just vlookups the id against a descriptor.

Otherwise it creates then syns, how would you recommend I bypass this problem? As syns I presume are not a great thing to have? As they seem to be junction tables to turn a many to many into a 1 to many relationship. Otherwise I am just creating a brand new table with the same data but a different name to get this working and it feels inefficient.

Many thanks,

James.

[Anonymous]

It looks like you have 2 roles that a person can perform :

• Account Manager
• Sales

For your visualisation requirements is there any need to have person as a dimension and have expressions relevant to both roles ?

I would suspect not as this would probably not return logically results.

[james_hanify]

An invoice needs to be there for historical significance as an account can be updated. I guess the only way is to duplicate the tables?

[Anonymous]

Your SalesPerson table only has 3 fields :

     Z_ID

     Name

     Group

So you could do a couple of Mapping Loads

    Using Z_ID to return Name

    Using Z_ID to return Group

Then a couple applymaps whilst loading the Invoice table creating 2 fields

     Invoice Sales Person

     Invoice Sales Group

And a couple more applymaps for the Account table creating

     Account Manager

     Account Manager Group

Thus you'll have no SalesPerson table in the resultant data model. ergo no synthetic keys or loops.