Is it possible to applymap to a Hierarchybelongs to?

[orangebloss]

Thanks to some help from this forum I've almost cracked the bringing down fields through a hierarchy. However, the solution includes applymap(Hierarchybelongsto..... and I'm struggling to understand why it's returning an error after Hierarchybelongsto - it's expecting a bracket or other punctuation but there is already one there?

HierarchyTable:
Hierarchy(TaskId, TaskParentTaskId, TaskName, ParentTaskId, ParentTaskName, Path, Depth)
LOAD
TaskId,
TaskParentTaskId,
TaskName
RESIDENT TasksTmp;

//Create mapping tables for ProjectType_TAG and Region_TAG
ProjectTypeMap:
MAPPING LOAD
TaskId,
ProjectType_TAG
RESIDENT TasksTmp
WHERE len(ProjectType_TAG) > 0;

RegionMap:
MAPPING LOAD
TaskId,
Region_TAG
RESIDENT TasksTmp
WHERE len(Region_TAG) > 0;

//Apply the mappings to propagate the tags down the hierarchy
FinalTable:
LOAD
TaskId,
TaskParentTaskId,
TaskName,
ApplyMap('ProjectTypeMap', HierarchyBelongsTo(TaskId, ParentTaskId), '') as ProjectType_TAG,
ApplyMap('RegionMap', HierarchyBelongsTo(TaskId, ParentTaskId), '') as Region_TAG
RESIDENT HierarchyTable;

[mpc]

Hi, 

HierachyBelongsTo is a Load/Select prefix, so you cannot use it as mentioned above. 
It's should be HierachyBelongsTo(NodeID, ParentID, NodeName, AncestorID, AncestorName). 

In your case, I think you'll achive what you want with:

FinalTable:

Load *, ApplyMap('ProjectTypeMap',TaskId) as ProjectType_TAG,
ApplyMap('RegionMap',TaskId) as Region_TAG;

HierachyBelongsTo(TaskId, TaskParentTaskId,TaskName,TaskParentTaskId, TaskParentTaskName) LOAD
TaskId,
TaskParentTaskId,
TaskName
RESIDENT HierarchyTable;

I don't know if you can use preceding load with HierachyBelongsTo, then you can replace it with a temp table.

Regards

[orangebloss]

HI Thanks for this but again as soon as I put in the Hierarchybelongsto the following text goes red.

Full script is as below

TimelogsTmp:
LOAD
    TimeId,
    dateCreated,
    createdAt,
    TotalMinutes,
    TotalHours,
    TotalWeeks,
    TimeDescription,
    timeLogged,
    "Date",
    "Month",
    "Year",
    "Year" & num("Month", '00') as YearMonth,
    "Day",
    deleted,
    dateDeleted,
    deletedAt,
    PeopleId,
    TaskId,
    ProjectId,
      deletedByUserId,
    deletedBy
FROM [Timelogs.qvd]
(qvd);

left Join

LOAD
    "Date",
    "Week",
    PeriodNum,
    "YY/PP",
    "YY/WW"
FROM [Data_Periods.qvd]
(qvd);

left join

LOAD
    PeopleId,
    FullName,
    JobTitle,
    Email,
    
    CompanyId
FROM [People.qvd]
(qvd);

left join

LOAD Name			as FullName, 
     [Primary Tech Skill],
     Role, 
     Location, 
     [Contract Status], 
     Type, 
     Team, 
     Subteam,
     Team & ' - ' & Subteam as TeamSubteam, 
     [Resource Category], 
     [Is Enabled]
FROM [Capacity.qvd]
(qvd);

left Join

Companies:
LOAD
    CompanyId,
    CompanyName
FROM [Companies.qvd]
(qvd);



NoConcatenate
Timelogs:
LOAD
    TimeId,
    dateCreated,
    createdAt,
    TotalMinutes,
    TotalHours,
    
    TotalWeeks,
    TimeDescription,
    timeLogged,
    "Date",
    "Month",
    "Year",
    YearMonth,
    "Day",
    deleted,
    dateDeleted,
    deletedAt,
    PeopleId,
    TaskId,
   
    
    Applymap('LeverMap',ProjectId)as Projectlever,
    deletedByUserId,
    deletedBy,
    "Week",
    PeriodNum,
    "YY/PP",
    "YY/WW",
    FullName,
    FullName & '-' & "Date" as NameDate,
    
   JobTitle,
    Email,
   
    CompanyId,
    [Primary Tech Skill],
    Role, 
    Location, 
   Type,
    Team, 
    Subteam,
    TeamSubteam, 
   TaskListNameMap,
   
    
    CompanyName,
    
Resident TimelogsTmp;

drop table TimelogsTmp;






Projects:
LOAD
     Description,
     ProjectId,
   
     ProjectName,
     ProjectType,
     BodyModel,
     Lever,
     Region,
     "Group",
     ProjectCode,
     Company,
        Category
FROM [Projects.qvd]
(qvd);


TaskLists:
LOAD
    TaskListId,
    TaskListName,
 
    TaskListDescription,
    TaskListProjectId as ProjectId,
    TaskListMilestoneId,
    TaskListMilestone,
    TaskListStatus,
    TaskListTaskId,
    TaskListTask
FROM [Tasklists.qvd]
(qvd);


TasksTmp:
LOAD
    TaskId,
    TaskName,
   
    if(not wildmatch(TaskName,'*:*'),applymap('TaskListNameMap',TaskListId),
     SubField(TaskName, ':', -2)) as  SectionNo,
        TaskDescription,
    if(len(TaskPriority)=0,'Low',Capitalize(TaskPriority)) as TaskPriority,
    TaskProgress,
    if(TaskProgress=100,'Complete','Incomplete') as Task_Completion_Status,
    TaskParentTask,
    TaskCard,
    TaskIsPrivate,
    TaskStatus,
               
    TaskEstimateMinutes,
    if(wildmatch(TaskName,'*Governance*'),TaskEstimateMinutes/60) as SOWHours,
    if(not wildmatch(TaskName,'*Governance*'),TaskEstimateMinutes/60) as EstimateHours,
    
    TaskEstimateMinutes/60 as TaskEstimateHours,
    
  
    TaskCreatedBy,
    date(TaskCreatedAt, 'DD/MM/YYYY hh:mm') as TaskCreatedAt,
    date(floor(TaskCompletedAt), 'DD/MM/YYYY') as TaskCompletedAt,
    TaskCompletedBy,
   
    TaskParentTaskId,
   Parent_Task,
    TaskListId,
 	applymap('TaskListNameMap',TaskListId) as Task_List_Name,
    MS_Deadline,
   
    Link as TaskLink,
  
    IsItAssigned,
    TagID,
    TagName,
  
     if(applymap('TagsNameMAP',applymap('TagIdsMAP',Link))='CME','CME',
if (applymap('TagsNameMAP',applymap('TagIdsMAP',Link)) ='NPD', 'NPD',
if (applymap('TagsNameMAP',applymap('TagIdsMAP',Link))='Non-Contract Specific','Non-Contract Specific',''))) as ProjectType_TAG,
if(applymap('TagsNameMAP',applymap('TagIdsMAP',Link)) = 'UK','UK',
if (applymap('TagsNameMAP',applymap('TagIdsMAP',Link)) ='APAC', 'APAC',
if (applymap('TagsNameMAP',applymap('TagIdsMAP',Link))='RoW','RoW',
if (applymap('TagsNameMAP',applymap('TagIdsMAP',Link)) ='Americas', 'Americas')))) as Region_TAG
FROM [Tasks.qvd]
(qvd);

left Join 


LOAD TaskId,
	 sum(TotalHours) as TaskTotalHours

   resident Timelogs
group by TaskId

;




left join

LOAD PeopleId as TaskCreatedByUserId, 
     FullName as TaskCreatedByName
FROM [People.qvd]
(qvd);

left join

LOAD PeopleId as TaskCompletedBy, 
     FullName as TaskCompletedByName
FROM [People.qvd]
(qvd);



NoConcatenate 
EstimateHoursTable:
 LOAD 
TaskId,
	 sum(TaskTotalHours)  as Hoursbooked,
     max(EstimateHours) as MaxEstimate,
     if( sum(TaskTotalHours)> max(EstimateHours),'Greater','Less') as BookedvsEstimateStatus2
resident TasksTmp
group by TaskId;

AssigneeUserIdTmp:
LOAD
    AssigneeUserId,
    Link as TaskLink
    
FROM [AssigneeUserIds.qvd]
(qvd);

left join

LOAD PeopleId as AssigneeUserId, 
     FullName as AssigneeUserName
    // 
FROM [lib://Engineering Dev:DataFiles/Teamwork_TeamworkPeople.qvd]
(qvd);

NoConcatenate
AssigneeUserId:
Load 
 AssigneeUserId,
 TaskLink,
 AssigneeUserName,
if(isnull(AssigneeUserName),'Not Assigned',AssigneeUserName) as Assigned_to

 
Resident AssigneeUserIdTmp;

Drop Table AssigneeUserIdTmp;

HierarchyTable:
Hierarchy(TaskId, TaskParentTaskId, TaskName, ParentTaskId, ParentTaskName, Path,'-', Depth)
LOAD
TaskId,
TaskName,
SectionNo,
TaskDescription,
TaskPriority,
Task_Completion_Status,
TaskParentTask,
TaskCard,
TaskStatus,
ADTaskStatus,
TaskStartDate,
SOWHours,
EstimateHours,
TaskParentTaskId,
Parent_Task,
TaskListId,
Task_List_Name,
TagID,
TagName
RESIDENT TasksTmp;

//Create mapping tables for ProjectType_TAG and Region_TAG
ProjectTypeMap:
MAPPING LOAD
TaskId,
ProjectType_TAG
RESIDENT TasksTmp
WHERE len(ProjectType_TAG) > 0;

RegionMap:
MAPPING LOAD
TaskId,
Region_TAG
RESIDENT TasksTmp
WHERE len(Region_TAG) > 0;

//Apply the mappings to propagate the tags down the hierarchy
FinalTable:
HierarchyBelongsTo(TaskId, TaskParentTaskId, TaskName, ParentTaskId, ParentTaskName,Depth)
LOAD
*,
ApplyMap('ProjectTypeMap', TaskId,TaskParentTaskId) as ProjectType_TAG1,
ApplyMap('RegionMap', TaskId, TaskParentTaskId) as Region_TAG1


RESIDENT HierarchyTable;
// Drop intermediate tables
DROP TABLE TasksTmp;
DROP TABLE HierarchyTable;