Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
orangebloss
Creator
Creator

Pivot table to show text field in measures

I'm trying to produce a pivot table that shows who each task is assigned to and the % complete that task is.

orangebloss_0-1752579940025.png

 

What I'm actually getting is just the one name - so if there is a Task3 with Nigel against it it also shows it as allocated to Task 2 and TaskName.  Is there a way around this? I need to be able to roll up the data for overall % complete which is why a straight table isn't sufficient but I can't find a away to show where more than one person is responsible for the subtasks?

Subtasks have been created using hierarchy:

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

 

I have tried mapping but the same issue occurs - any help or suggestions appreciated


Carol

 

Labels (3)
5 Replies
Or
MVP
MVP

It sounds like you're trying to undo the hierarchy you've created in the first place, but I don't quite understand what the expected outcome is in this case. Is it something like:

Task3 - Nigel && Geoff && Bob (as a concatenated single field)

or

Task 3 - Nigel | Geoff | Bob (in three different columns)

or perhaps,

Task3 - Nigel

Task3 - Geoff

Task3 - Bob

or something else entirely? 

 

orangebloss
Creator
Creator
Author

So the plan is that the tasks are in a hierarchy 

 

Bob is responsible for the overall TaskName

Geoff is overall responsible for Task2

Nigel is responsible for Task3

 

In a flat table the person allocated to the task can be seen against the task but in a pivot table only one name is put against all 3 (assuming this is something to do with rolling up?) 

orangebloss_0-1752594457519.png

 

 

 

Or
MVP
MVP

Could you add the actual data and code you're using (the sample, not the full original, of course)? I'm still not quite following what steps you're taking or what result you're getting. 

orangebloss
Creator
Creator
Author

The data is combined in a few temporary tables but the hierarchy happens in the last table where everything is pulled together from the final temp table (so Load *) but these are the fields that are affected.

 

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

LOAD * Inline[

TaskId, TaskparentTaskId,TaskName,TaskParentTask,AssignedTo,
1, , TaskName, , Bob,
2, 1, Task1, TaskName, Geoff,
3, 2, Task2, Task1, Nigel
];

Or
MVP
MVP

Okay, so what you're actually building is this:

Or_0-1752662192006.png

Which means "Taskname" is associated with all three people, Task1 is associated with two people, and only Task2 is associated with a single person. 

There's likely a number of ways to deal with this. I went with this one (Note: Dimensions have been set to not show nulls)

Or_1-1752662927356.png

I'm not sure if that covers your use case since you mentioned rolling up completion percentages, but those aren't included in the data you've provided so I can't say.