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
