Ggtcf

I have 3 sets of objects, 1 Set can be map to a corresponding set by a unique key. That same set can be mapped to the 3rd set by a different key. I need to be able to map all of these into a new combined set. These sets all have different properties.

Unique Count [users][sector]

Many Count [invoices]

Each unique [user] belongs to a specific [sector] found by (comitID), that same [user] can have many [invoices] though. A one to many field if your familiar with Relational Databases

const users= [ // Unique Entries
 name:'user1', comitId: 'aa1',
 name:'user2', comitId: 'aa2'
]

const sector= [ // Unique Entries
 comitID: 'aa1', department: 'finance',
 comitID: 'aa2', department: 'marketing',
 comitID: 'aa3', department: 'engineering'
]

const invoices= [ // Multiple Entries
 name: 'user1' : statementDate: '2/1/2019',
 name: 'user1' : statementDate: '2/14/2019',
 name: 'user2' : statementDate: '2/1/2019'
]

The new set should look like this. Cannot contain a list for the statement dates, they each need to be a new object.

const results = [
 name: 'user1', comitId: 'aa1', department: 'finance', statementDate: '2/1/2019',
 name: 'user1', comitId: 'aa1', department: 'finance', statementDate: '2/14/2019',
 name: 'user2', comitId: 'aa2', department: 'marketing', statementDate: '2/1/2019'
]

I have been trying this in Excel with vlookups and formulas. These files tend to be 10k for the unique counts and up 40k for the invoices.

You can use Array.map() over invoices and Array.find() to get the corresponding entries in users and then sectors:

const users = [ // Unique Entries
 name:'user1', comitId: 'aa1',
 name:'user2', comitId: 'aa2'
];

const sectors = [ // Unique Entries
 comitID: 'aa1', department: 'finance',
 comitID: 'aa2', department: 'marketing',
 comitID: 'aa3', department: 'engineering'
];

const invoices = [ // Multiple Entries
 name: 'user1', statementDate: '2/1/2019',
 name: 'user1', statementDate: '2/14/2019',
 name: 'user2', statementDate: '2/1/2019'
];

const result = invoices.map(invoice => );

console.log(result);

I would suggest that you just iterate over the invoices and enrich the entries with the ones from the sets with unique entries.

Something like this (does not tested the code, but I hope you can understand the idea)

const data = invoices
 .map(entry => ...entry, ...
 users.find(user => user.name === entry.name)
 .map(user => ...user,
 sector.find(sec=> sec.comitID === user.commitID)
 )
 
 )

You could improve speed when you first create a map out of the sets and then just lookup the join attributes instead of searching for them

const userMap = users.reduce((map, user) => ...map, ...user.name: user, )
const sectorMap = sector.reduce((map, sec) => ...map, ...sector.comitID: sec), )

const data = invoices.map(invoice => ...invoice, ...userMap[invoice.name], ...sector[userMap[invoice.name].comitID])

Here's a basic script that could work.

const users = [ // Unique Entries
 name:'user1', comitId: 'aa1',
 name:'user2', comitId: 'aa2'
]

const sectors = [ // Unique Entries
 comitID: 'aa1', department: 'finance',
 comitID: 'aa2', department: 'marketing',
 comitID: 'aa3', department: 'engineering'
]

const invoices = [ // Multiple Entries
 name: 'user1', statementDate: '2/1/2019',
 name: 'user1', statementDate: '2/14/2019',
 name: 'user2', statementDate: '2/1/2019'
]

sectors.forEach(sector => 
 const user = users.find(user => sector.comitID === user.comitId);
 if (user) 
 user.department = sector.department;
 
);

const results = invoices.map(invoice => 
 const user = users.find(user => invoice.name === user.name);
 return Object.assign(, user, statementDate: invoice.statementDate );
);

console.log(results);

You can use map & filter

const users = [ // Unique Entries
 
 name: 'user1',
 comitId: 'aa1'
 ,
 
 name: 'user2',
 comitId: 'aa2'
 
]

const sector = [ // Unique Entries
 
 comitID: 'aa1',
 department: 'finance'
 ,
 
 comitID: 'aa2',
 department: 'marketing'
 ,
 
 comitID: 'aa3',
 department: 'engineering'
 
]

const invoices = [ // Multiple Entries
 
 name: 'user1',
 statementDate: '2/1/2019'
 ,
 
 name: 'user1',
 statementDate: '2/14/2019'
 ,
 
 name: 'user2',
 statementDate: '2/1/2019'
 
]
let newArray = invoices.map(function(item) 
 // this value will be use to find match between users & sectors
 let cId = users.filter(user => user.name === item.name)[0].comitId;
 return 
 name: item.name,
 statementDate: item.statementDate,
 comitId: cId,
 department: sector.filter(sector => sector.comitID === cId)[0].department
 
);

console.log(newArray)

You could move user and sector items into a map and take this object, if necessary.

const
 users = [ name: 'user1', comitId: 'aa1' , name: 'user2', comitId: 'aa2' ],
 sector = [ comitID: 'aa1', department: 'finance' , comitID: 'aa2', department: 'marketing' , comitID: 'aa3', department: 'engineering' ],
 invoices = [ name: 'user1', statementDate: '2/1/2019', name: 'user1', statementDate: '2/14/2019' , name: 'user2', statementDate: '2/1/2019' ],
 setMap = k => (m, o) => m.set(o[k], o),
 userMap = users.reduce(setMap('name'), new Map),
 sectorMap = sector.reduce(setMap('comitID'), new Map),
 result = invoices.map(( name, statementDate ) => 
 var comitId = userMap.get(name),
 department = sectorMap.get(comitId);
 return name, comitId, department, statementDate ;
 );

console.log(result);

.as-console-wrapper max-height: 100% !important; top: 0;