Sorry, Community, but I still struggle with the syntax of this tool and could use some help.
I've attached a sample of my data set. The two tabs show the Original State of the data and the Desired State of the data. While I'm actually pulling this from SQL, the same logic will apply to this simplified spreadsheet.
In words, the logic would read something like, "If the Status = "Deleted", then the course_id should inherit the value of the last course_id row above that has a Status = "Active." If the Status is already "Active" the row should remain unchanged.
The sample file is color-coded to show some of the possible combinations. In each example, you'll notice that the course_name fields are identical strings.
Appreciate any help you might offer! Once I get this tool fully mastered, I hope to attack RegEx. We'll see how much my brain can handle.
Solved! Go to Solution.
Hi,
I can't test this in Alteryx right this second, so my syntax may also be slightly off, but I believe you should be able to use the following formula:
IF [Status] = 'DELETED' THEN [row-1:course_id] ELSE [course_ID] ENDIF
Thanks, Claje, I'll give this a shot.
I'm wondering if this will work if the row directly above it also has a status = Deleted?
Assuming I've understood your use case correctly, I think it will handle this appropriately.
Basically, the Multi-Row formula is calculated line by line from the top to the bottom.
as such, if we had a sequence like the below:
Course_ID | Status |
1234 | Active |
5678 | Deleted |
9102 | Deleted |
We would expect that the final result should be:
Course_ID | Status | Comment |
1234 | Active | |
1234 | Deleted | Follows the main rule and grabs the Course_ID from the row above |
1234 | Deleted | Checks the row above to get the Course_ID. Since the previous row was already updated with the prior valid Active course, this should be the same |
Note that this assumes that Status is binary (active or deleted only), and you may need to add more logic if there are additional statuses that you want to ignore.
Hm, since it's not guaranteed that the value you're looking for will always be directly above (as in the biology class in the last 3 rows of your example), this will be a bit tricky using the Multi-Row tool. *(see edit)* I'd probably approach it using something like the workflow below.
Since it appears the course name is your "unique ID" for each course, I've used a Summarize tool, grouping on Course Name and Status, and taking the last course_id for each of those groups.
I then filter to only take the active ones, and join the data back into the main set on Course Name.
Then, using a regular formula tool, you can set course_id = Last_course_id if Status=Deleted.
Let me know if there's any problems with this, or you need clarification,
Cheers!
EDIT: Ah, yes I've completely forgotten that the prior cells would have been evaluated already and replaced if necessary, and so checking the one above it should suffice. My solution still accomplishes the goal, but the others are still correct and more concise
Worked like a charm. Thanks again, everyone!
ivoller -- Appreciate the attached workflow.
tcroberts -- Also appreciate the logic. I think I am good because of the way I had already sorted the data. I also SHOULD have known that this is a linear tool, starting at the top and progressing through all the rows.