Hi all, im pretty new to alteryx and i need some help:
This is my input file:
| Name | Department | Team | Sub-team | Tag to project | Jan20 | Feb20 | Mar20 | Apr20 | May20 | Jun20 | Jul20 | Aug20 | Sep20 | Oct20 | Nov20 | Dec20 | Jan21 |
| John | A | A1 | A1-1 | | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| John | A | A1 | A1-2 | | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 |
| John | B | B1 | B1-3 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 |
| Sam | C | C1 | C1-2 | C | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 |
| Sam | C | C1 | C1-2 | B | 0 | 0 | 0 | -1 | -1 | -1 | -1 | -1 | -1 | -1 | -1 | -1 | -1 |
| Sam | C | C1 | C1-2 | A | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 |
| Mary | B | B2 | B2-1 | B | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 |
| Mary | A | A2 | A2-1 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 |
Context: I am trying to track the movement of the people in the company based on the current month (i.e. November 20) and each line/row represents 1 movement, and "0" or "-1" means that this person is not longer in that particular department, team, sub-team & tagging to project for the month.
eg. For JOHN:
row 1: John was in Dept A, Team A1, subteam A1-1, and not tagged to any project, from Jan20 to May20 [old movement]
row 2: then John moved to Dept A, Team A1, subteam A1-2, and not tagged to any project, from Jun20 to Sep20 [old movement]
row 3: then John moved to Dept B, Team B1, subteam B1-3, and not tagged to any project, from Oct20 to Jan21 [current movement]
eg. for SAM:
row4: Sam is in Dept C, Team C1, subteam C1-2, tagged to project C, from Apr20 to Jan 21 [current movement]
row 5: Sam was in Dept C, Team C1, subteam C1-2, tagged to project B, from Apr20 to Jan 21 [old movement]
row 6: Sam was in Dept C, Team C1, subteam C1-2, tagged to project !, from Feb20 to Jan 21 [old movement]
--> from Sam example, we can see that the sequence of events is jumbled up, Row 6 happened first, then Row 5 then Row 4
**for row 5 &6, Sam, note: the -1 means that he is being removed from the tagging of project A (because -1) and that line is tagged to project B because we want to have a -1 line under Project B (for internal calculations)
This is the output i desire - column in red, i want to identify which lines/row are the old movement or New (current), any future movements are considered as New/current movements too:
| Name | Department | Team | Sub-team | Tag to project | Jan20 | Feb20 | Mar20 | Apr20 | May20 | Jun20 | Jul20 | Aug20 | Sep20 | Oct20 | Nov20 | Dec20 | Jan21 | Old/New? |
| John | A | A1 | A1-1 | | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | old |
| John | A | A1 | A1-2 | | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | old |
| John | B | B1 | B1-3 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | new |
| Sam | C | C1 | C1-2 | C | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | new |
| Sam | C | C1 | C1-2 | B | 0 | 0 | 0 | -1 | -1 | -1 | -1 | -1 | -1 | -1 | -1 | -1 | -1 | old |
| Sam | C | C1 | C1-2 | A | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | old |
| Mary | B | B2 | B2-1 | B | 0 | 0 | 0 | 0 | 0 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | new |
| Mary | A | A2 | A2-1 | | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | new |
I have thought of using Multi Row as a tool but i am not sure if this is the correct tool to use ..? and i also have no idea how to frame the conditions
EDIT: Mary has 2 "New" because her 2nd line is a forecasted movement, so she is forecasted to move to a new team in Jan21 (she will be in the first team from Jan20 to Dec20 then move to another team in Jan21), so moving to another team creates another line/row.
Thank you all in advance, really appreciate your help! 🙂 apologies that it is a long wall of text.
