MultiRow IF Statement to return the value X rows prior (it varies)

[alphabex18]

Hi All, 

I would like to change the current value of the column Leading Qualifier, but i dont know how to write a proper if statement within a multi-row formula tool.

Here is the logic.. IF(SegmentID="N301", then Return the value in the LeadQualifier column that corresponds with the most recent occurrence of NM101. Sometimes the NM101 is 13 rows prior, and sometimes it is only 2 rows priorm but it alwys has to look back, and not forward. I'm at a loss on how to write that in Alteryx.

Much appreciated!

Right_Loop Header	SegmentID	LeadingQualifier
2100A Member Name	NM101	IL
2100A Member Name	NM102	IL
2100A Member Name	NM103	IL
2100A Member Name	NM104	IL
2100A Member Name	NM105	IL
2100A Member Name	NM106	IL
2100A Member Name	NM107	IL
2100A Member Name	NM108	IL
2100A Member Name	NM109	IL
2100A Member Name	PER01	IP
2100A Member Name	PER02	IP
2100A Member Name	PER03	IP
2100A Member Name	PER04	IP
	N301	123 ABC Drive

[jasperlch · Accepted solution]

Hi @alphabex18, 

I would suggest using a Multi Row Formula to find LeadingQualifier of the most recently NM101 and then remove all the values for this new column except for N301:

[alphabex18 · Accepted solution]

jasperlch TO THE RESCUE!!!

Thank you, worked perfect. I am one step closer to cleaning complicated EDI loops. I will have more roadblocks, and i am so happy that Alteryx has such a great community of helpers. Even on a Sunday.