I have a data set as follows Objective IDObjective NameObjective StartObjective FinishKey Result IDKey Result NameKey Result Start DateKey Result Finish DateTask NameTask FinishInv FlagIssue NameIssue Resolution DateRisk NameRisk Resolution DateOB02OBJ 0213-06-202431-12-2026KR4Test 113-06-202413-06-2024Task 130-09-2024KD(blank)(blank)(blank)(blank)OB02OBJ 0213-06-202431-12-2026KR4Test 113-06-202413-06-2024Task 231-12-2026KD(blank)(blank)RSK130-11-2025OB02OBJ 0213-06-202431-12-2026KR4Test 113-06-202413-06-2024Task 331-12-2026KDIss0130-06-2026RSK130-11-2025OB02OBJ 0213-06-202431-12-2026KR4Test 113-06-202413-06-2024Task 530-09-2021KD(blank)(blank)(blank)(blank)OB02OBJ 0213-06-202431-12-2026KR5Test 213-06-202413-06-2024Task 131-12-2026KD(blank)(blank)(blank)(blank)OB02OBJ 0213-06-202431-12-2026KR6Test 313-06-202413-06-2024Task 201-01-2025KD(blank)(blank)(blank)(blank)OB02OBJ 0213-06-202431-12-2026KR7Test 413-06-202413-06-2024Task 131-12-2026KD(blank)(blank)(blank)(blank)OB02OBJ 0213-06-202431-12-2026KR8Test 507-04-202507-04-2025Task 1030-11-2027KD(blank)(blank)(blank)(blank)OB02OBJ 0213-06-202431-12-2026KR9Test 607-04-202507-04-2025Task 131-12-2026KD(blank)(blank)(blank)(blank)OB02OBJ 0213-06-202431-12-2026KR9Test 607-04-202507-04-2025Task 231-12-2026KD(blank)(blank)RSK230-11-2025 I want to create a data structure to set up a hierarchy in a pivot table based on parent child OBJ - highest, then KR , then Task then risk issues but a unique hierrachy What I want is something like this : TypeNameStart DateEnd DateFlagKD- OBJOBJ 0213-06-202431-12-2026KRTest 113-06-202413-06-2024MSTask 1-30-09-2024RIRisk Name- IsIssue Name- MSTask 2 31-12-2026RIRSK1 30-11-2025IsISS01 30-06-2026 THe dates should be summarised as start and end with end dates only for risk and issues If the KR is repeated for multiple tasks and tasks linked to risk and issues I should see the KR only once and the related task only once as above

Create a hierarchical OKR data set

SouravKayal

I have a data set as follows

Objective ID

Objective Name

Objective Start

Objective Finish

Key Result ID

Key Result Name

Key Result Start Date

Key Result Finish Date

Task Name

Task Finish

Inv Flag

Issue Name

Issue Resolution Date

Risk Name

Risk Resolution Date

OB02

OBJ 02

13-06-2024

31-12-2026

KR4

Test 1

13-06-2024

Task 1

30-09-2024

(blank)

OB02

OBJ 02

13-06-2024

31-12-2026

KR4

Test 1

13-06-2024

Task 2

31-12-2026

(blank)

RSK1

30-11-2025

OB02

OBJ 02

13-06-2024

31-12-2026

KR4

Test 1

13-06-2024

Task 3

31-12-2026

Iss01

30-06-2026

RSK1

30-11-2025

OB02

OBJ 02

13-06-2024

31-12-2026

KR4

Test 1

13-06-2024

Task 5

30-09-2021

(blank)

OB02

OBJ 02

13-06-2024

31-12-2026

KR5

Test 2

13-06-2024

Task 1

31-12-2026

(blank)

OB02

OBJ 02

13-06-2024

31-12-2026

KR6

Test 3

13-06-2024

Task 2

01-01-2025

(blank)

OB02

OBJ 02

13-06-2024

31-12-2026

KR7

Test 4

13-06-2024

Task 1

31-12-2026

(blank)

OB02

OBJ 02

13-06-2024

31-12-2026

KR8

Test 5

07-04-2025

Task 10

30-11-2027

(blank)

OB02

OBJ 02

13-06-2024

31-12-2026

KR9

Test 6

07-04-2025

Task 1

31-12-2026

(blank)

OB02

OBJ 02

13-06-2024

31-12-2026

KR9

Test 6

07-04-2025

Task 2

31-12-2026

(blank)

RSK2

30-11-2025

I want to create a data structure to set up a hierarchy in a pivot table based on parent child

OBJ - highest, then KR , then Task then risk issues but a unique hierrachy

What I want is something like this :

Type	Name	Start Date	End Date
Flag	KD	-
OBJ	OBJ 02	13-06-2024	31-12-2026
KR	Test 1	13-06-2024	13-06-2024
MS	Task 1	-	30-09-2024
RI	Risk Name	-
Is	Issue Name	-
MS	Task 2		31-12-2026
RI	RSK1		30-11-2025
Is	ISS01		30-06-2026

THe dates should be summarised as start and end with end dates only for risk and issues

If the KR is repeated for multiple tasks and tasks linked to risk and issues I should see the KR only once and the related task only once as above

Data Investigation

Iterative Macro

Accepted answers

All comments

SouravKayal

How can i achieve this

jrlindem

Can you elaborate more on how your data goes from the first table to the second? There seems to be a lot of custom arranging where the pattern is not easy to follow. -Jay

SouravKayal

The Type becomes if its an objective , KR , task , risk or issue and all names must be appearing under a name column with their respective dates as start and end date

SouravKayal

Can anyone please help me with this ?

Imaizumi

As @jrlindem pointed out it is not clear how you go from your data to your output.

But if I have to take a guess, then create a batch macro that filter by your Key Result ID (I assume this is your main key).

For each Key Result ID, then create some helper columns via Formula, multi-row formula etc to generate the desired order.
Have the batch macro output each Key Result ID. You might need to create some helper Key columns you can sort your data by.

If this does not solve your problem, then you need to break it more down on exactly how you get to your output so people can better understand and help.

SouravKayal

The hierarchy need sto be a parent child obj being the highest parent linked KR , KR linked to tasks and tasks to risk and issues. I want to show a nested hierarchy in a pivot so need to arrange the data accordingly but struggling with the parent child relationship

jrlindem

@SouravKayal

As you've laid this ask out, in my opinion, it is not solvable. I am (and others are) not following your explanations. The provided data and result do not appear to relate to each other with the absence of logic and context that explain how the data is transformed or summarized.

Consider the following (sorry for the craziness of colors):

You can see that from the guidance you have provided it is very difficult to track the movement of the data. Translating this to an Alteryx workflow would require more intimate knowledge of the data and your ask.

I think the only way the community could help support would be for you to attach an excel document with your starting data, along with the output table, but in a way that shows the formulas and/or pivot table behavior so that we can track and attempt to replicate the logic. Unfortunately, just providing the summarized data without the formulas would not be helpful.

If this is not possible, I understand, but then would not be able to provide any further support.

Either way, best of luck and I hope you are able to figure out a solution.

Cheers, -Jay

OllieClarke

Hi @SouravKayal

If I've understood your problem, I think this workflow does what you want:

It's basically repeating the same steps in an iterative way, and reordering your cells. I'm not 100% happy with the workflow, as there is probably a more elegant solution, but it does at least (I think) get to the desired end based on your question.

The container in the middle adds dummy issue and risk ("Issue Name"/"Risk Name") to Tasks/Risks which don't have them. I'm not sure if it's needed, but I thought it might be from your question.

Hope that helps,

Ollie

SouravKayal.yxmd

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335