We have below raw data -
ID Extension No EXTENDS_ID EXTENDED_BY_ID EXPECTED Finish DATE
56,868 67,204 6/21/2019
67,204 1 56,868 91,137 2/5/2020
70,384 80,472 4/15/2020
76,075 92,927 8/15/2020
80,472 1 70,384 86,063 11/18/2020
86,063 2 80,472 93,626 3/11/2021
91,137 2 67,204 7/2/2021
92,927 1 76,075 8/11/2021
93,398 96,202 8/20/2021
93,626 3 86,063 8/25/2021
96,202 1 93,398 10/19/2021
ID are extended and have column how many times(Extension No) it has been extended as below -
1) 56868 - 67204 - 91137
2) 70384 - 80472 - 86063 - 93626
3) 76075 - 92927
4) 93398 - 96202
Requirement is.. We have to display highest ID number and lowest Finished date to its previous linked ID(initially created ID) and take expected finish date from 1st generated ID.
For example in in 1st scenario 91137 ID and Expected finish date from 56868 ID i.e. 6/21/2019.
Please help me to find out the solution for this in Alteryx.
Try this workflow out. The "Make Groups" building block does exactly what you're looking for, shown in the center of this workflow. It relates all rows of a specific hierarchy.
|
Please mark this as solution if it does what you intend.
Thanks a lot Joel for the solution. It was a great help to make use of "Make group" transform and learning of it.
Small addition in the requirement.
We have two more field ranking
ID Extension No EXTENDS_ID EXTENDED_BY_ID EXPECTED Finish DATE Rank1 Rank2
56,868 67,204 6/21/2019 1 1
67,204 1 56,868 91,137 2/5/2020 1 1
70,384 80,472 4/15/2020 4 4
76,075 92,927 8/15/2020 1 1
80,472 1 70,384 86,063 11/18/2020 4 4
86,063 2 80,472 93,626 3/11/2021 4 4
91,137 2 67,204 7/2/2021 3 3
92,927 1 76,075 8/11/2021 5 5
93,398 96,202 8/20/2021 0 4
93,626 3 86,063 8/25/2021 4 4
96,202 1 93,398 10/19/2021 0 0
We have to select previous linked ID finish date only if rank1 or rank 2 values are >= 3. If its less than then we have to ignore previous finish date.
In above example
1) 56868 - 67204 - 91137 -----> 91137 & associated expected finish date
2) 70384 - 80472 - 86063 - 93626 ----> 70384 & associated expected finish date
3) 76075 - 92927 ----> 92927 & associated expected finish date
4) 93398 - 96202 ---> 93398 & associated expected finish date
Any suggestion would be a great help to complete this requirement.
@AnkeshGoyal you could add that as a filter after the current filter in the workflow. I would use Rank1 >= 3 OR Rank2 >= 3. before the make groups tool.
If the solution above is correct, please select it as the chosen solution.