Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi-Row Formula Assistance

gene_denny
8 - Asteroid

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.

 

 

 

 

6 REPLIES 6
Claje
14 - Magnetar

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
8 - Asteroid

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?

Claje
14 - Magnetar

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_IDStatus
1234Active
5678Deleted
9102Deleted

 

We would expect that the final result should be:

Course_IDStatusComment
1234Active 
1234DeletedFollows the main rule and grabs the Course_ID from the row above
1234DeletedChecks 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.

ivoller
12 - Quasar

see attached

tcroberts
12 - Quasar

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
filldeletedcourses.PNG

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

gene_denny
8 - Asteroid

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.

Labels