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

Question

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 HeaderSegmentIDLeadingQualifier2100A Member NameNM101IL2100A Member NameNM102IL2100A Member NameNM103IL2100A Member NameNM104IL2100A Member NameNM105IL2100A Member NameNM106IL2100A Member NameNM107IL2100A Member NameNM108IL2100A Member NameNM109IL2100A Member NamePER01IP2100A Member NamePER02IP2100A Member NamePER03IP2100A Member NamePER04IP N301 123 ABC Drive

jasperlch · Accepted Answer

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 Answer

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.