Creating a loop / Array function

[Felix776]

Hi Alteryx Users,

Quite new to Alteryx, and would like if somebody can shed some light on this? I am quite sure this has already achieved in Alteryx, but I couldn’t find it in the forum

I am trying to create a "condition if, else, then" formula in a loop/array function. My apologies on the excel terminologies.

I process the table below with the following condition, criteria and sequence, and make a mark in a column.

Date has been first sorted in ascending order in the order of serial no. then Activated Time

• Look at the serial no. and find the same number. 
• Focus in the same serial no. as a loop.
• Inspect the Activated Times (A) and Clear Times (C) of the same serial no.
• If it is the first entry of the particular serial no. => Ignore the row

3.1.) Then. Check if conditions met, Z override Y, Y override X, X override W. if condition is met then return value from the same row in column Signal in a new column

        W.) the current Activated Time (A2) is the same as Last entered Activated Time (A1) AND the current Cleared Time (C2)  is the same as Last entered Cleared Time (C1).

         X.) the current Activated Time (A2) is the same as Last entered Activated Time (A1) AND the current Cleared Time (C2) is the NOT the same as Last entered Cleared Time (C1).

         Y.) the current Activated Time (A2) is the NOT the same as Last entered Activated Time (A1) AND the current Cleared Time (C2) is the NOT the same as Last entered Cleared Time (C1).

         Z.) the current Activated Time (A2) is the NOT the same as Last entered Activated Time (A1) AND the current Cleared Time (C2) is the NOT the same as Last entered Cleared Time (C1). AND Last entered Cleared Time (C1) is in the future of the current Activated Time (A2) 

        K.) Difference between the current Activated Time (A2) Last entered Cleared Time (C1) > |50 minutes|

This is the output I want.

Serial no.	Mode	Variation		Activated Time		Cleared Time	Signal	Duration (min)	NEW COLUMN
452803	A	3		30/09/2019 14:12:46		01/10/2019 07:26:02	Red	1033.3	RED
452803	A	3		25/11/2019 09:27:45		25/11/2019 10:29:44	Red	62
452803	A	3		25/11/2019 10:49:44		04/02/2020 11:06:51	Red	102257.1	RED
460006	D	2	A1	17/07/2019 10:08:25	C1	18/07/2019 08:01:09	Green	1312.7
460006	D	2	A2	17/07/2019 10:08:25	C2	18/07/2019 08:01:09	Amber	1312.7	Amber
460006	D	2	A3	06/02/2020 14:03:04	C3		Amber	Open	Amber
460006	D	3	A4	08/02/2020 20:32:48	C4	09/02/2020 08:53:39	RED	740.9	RED
490571	D	2		18/07/2019 14:58:36		18/07/2019 15:05:07	Green	6.5	Amber
490571	D	2		18/07/2019 15:59:07		24/07/2019 06:20:17	Green	8061.2	Amber

Hope this is clear!

Many Thanks

[T_Willins]

Hi @Felix776,

This is hard to follow without source data.  You have provided the desired output, but not the initial data.  If you can attach the initial data that matches your desired out output the Community may be able to help with this.  what you are looking for should be possible.

[demand_james · Accepted solution]

This looks like it would be easy enough to do with a multi-row formula tool.

Agree sending a bit of sample data would make it easier to follow.