NO CODING SOLUTION
It is a tricky thing to fill up values from one row above to the current row. In Alteryx you can use the Multi Row formula tool which is not part of Tableau Prep (September 2022).
Following example:
Here is how to fill out the store managers:
First of all it is necessary to have a row ID or another unique identifier for the rows. If there is no one create a row ID with the Rank tool. In this case the Row ID is given.
1. Clean step and remove the rows with a null value in the “Store Manager” column
2. Remove all columns except the Row ID and the Store Manager
3. Join the result with the original table, the join clause is the Row ID
4. Change the join clause to >=. On the left side is the Row ID from the original table, on the right side is the table we created in step 2 (result should have 17 rows)
WHY?
We do this, because we assigned every store manager to every row downwards. Jenny (Row ID 1) is assigned to every other Row ID. Make sense, because we said in the join clause every row ID greater or equal to should be joined. For the other managers it’s only the ones “above” their row id. The reason is, that it doesn’t matter with this method how many rows with NULL values come after the last value, i.e. Jonathan.
5. So we have now filled out the NULLs in a separate column, but some values are too often in the table (Jenny and Tom)
To fix this we use a FIXED LOD on the Row ID-1 where we say: Group by: Row ID and take the MAX from Row ID-1
WHY?
Because we assigned every manager (and his/her ID) to every Row ID we assigned some names to values which aren’t correct, i.e. Jenny is assigned to everything.
We take a look for every Row ID the corresponding Row ID-1, in Row ID-1 are only the IDs of the rows with the manager names, we are interested in. The other rows contain the information which are filled out in the whole table, i.e. Store and Sales Target.
Row 2 contains information from Jenny. We take the row ID (2) and look the MAX value in the Row ID-1 up, which is 1. By doing this we assign the correct Store Manager to the right Row IDs. Until Row ID 3 it is jenny, from Row ID = 4 the MAX value in the Row ID-1 is 4 and the corresponding name is Tom.
6. Now we just have to filter the Max Row ID we created with the FIXED LOD. We want to know if Max Row ID = Row ID-1.
In the Store Manager-1 are now the correct store managers for the correct row.
7. Clean everything up --> remove unnecessary rows and rename fields --> DONE