Allocate value from Hierarchy level to a lower lever

[orangebloss]

HI

I've created a hierarchy based on Task ID

Hierarchy ( TaskId,TaskParentTaskId,TaskName,'ParentTask')

On the first level of the hierarchy there is an additional field that I could do with pulling down to all levels below it :

if(TagName = 'CME','CME',

if (TagName ='NPD', 'NPD',

if (TagName='Non-Contract Specific','Non-Contract Specific',''))) as ProjectType_TAG,

if(TagName = 'UK','UK',
if (TagName ='APAC', 'APAC',
if (TagName='RoW','RoW',
if (TagName ='Americas', 'Americas')))) as Region_TAG

This tag only appears on the 'parent' of the hierarchy (essentially I'm pulling through a task list - all the subtasks should have the same data but the source does not contain that). The TagName contains several key words so I'm only looking for specific ones.

In the example below anything with an xxx should contain the same data as the related TaskName e.g. Subtask1 should have the same Region_TAG as TaskName1 - is this even possible? At the moment I'm losing data in my charts if I try to filter on Region/project type tags which is understandable but I want both!

Task	ProjectType_TAG	Region_TAG
TaskName1	CME	UK
Subtask1	xxx	xxx
Subtask1.2	xxx	xxx
TaskName2	NPD	RoW
Subtask2	xxx	xxx
Subtask2.1	xxx	xxx

[F_B]

Hi @orangebloss ,

could this be what you are looking for?

RawData:
LOAD
TaskId,
TaskParentTaskId,
TaskName,
if(TagName = 'CME','CME',
if (TagName ='NPD', 'NPD',
if (TagName='Non-Contract Specific','Non-Contract Specific',''))) as ProjectType_TAG,
if(TagName = 'UK','UK',
if (TagName ='APAC', 'APAC',
if (TagName='RoW','RoW',
if (TagName ='Americas', 'Americas')))) as Region_TAG
FROM [YourDataSource];

//Create the hierarchy
HierarchyTable:
Hierarchy(TaskId, TaskParentTaskId, TaskName, ParentTaskId, ParentTaskName, Path, Depth)
LOAD
TaskId,
TaskParentTaskId,
TaskName
RESIDENT RawData;

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

RegionMap:
MAPPING LOAD
TaskId,
Region_TAG
RESIDENT RawData
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;

// Drop intermediate tables
DROP TABLE RawData;
DROP TABLE HierarchyTable;

[orangebloss]

Hi, Apologies I'd missed your response I'll see if I can make this work and feed back!

[orangebloss]

I'm having a little trouble with the final table? I'm not sure why the red?

[F_B]

No worries

[F_B]

I'll look into it to see what's going on

[F_B]

Meanwhile, you should achieve your goal also avoiding HierarchyBelongsTo, try like this:

FinalTable:
LOAD
TaskId,
TaskParentTaskId,
TaskName,
ApplyMap('ProjectTypeMap', TaskParentTaskId,
ApplyMap('ProjectTypeMap', TaskId, '')) as ProjectType_TAG,
ApplyMap('RegionMap', TaskParentTaskId,
ApplyMap('RegionMap', TaskId, '')) as Region_TAG
RESIDENT HierarchyTable;