Here is improved description to the question I asked. Pls see the attached documents.
Instead of using a link table, I have used Hierarchy function. I need the following: when I select Tom under Dir Name, it should be associated with John and Chris under Mgr Name and when John is selected, it should be associated with Personnel 1 under Staff Name. Below is the code I have but it is not working. Help would be really appreciated.
LOAD [Exec Type],
if( [Exec Type] = 'Dir', [Exec Name]) as [Dir Name],
if( [Exec Type] = 'Mgr', [Exec Name]) as [Mgr Name],
if( [Exec Type] = 'Staff', [Exec Name]) as [Staff Name],
[sample excel files\hierarchy.xlsx]
(ooxml, embedded labels, table is Sheet1);
Hierarchy(NodeID, ParentID, 'Exec Type', 'Parent Location', 'Location',
'PathName', '~', 'Depth')
LOAD * INLINE [
NodeID, Exec Type, ParentID
2, Mgr, 1
3, Staff, 2