Multi-Row Formula Assistance

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.

Sample Data.xlsx

Preparation

Accepted answers

Claje

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

All comments

Claje

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

gene_denny

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?

ivoller

see attached

MR tool.yxmd

tcroberts

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.

Spoiler

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

filldeletedcourses.yxmd

gene_denny

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.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703