Sequence of activities

[svergtanya]

Hi,

I would like to check that sequence of activities per every unique ID is correct or not. It should be based on the timestamp.

Out of all activities (can be repetitive), I need to check that the main 7 have happened and the order of them was correct.

Example below:

ID	Timestamp	Activity	Correct
123	2020-10-01 01:01:13	AC	Yes
123	2020-10-01 01:01:14	AC	Yes
123	2020-10-01 01:01:15	DC	Yes
123	2020-10-01 01:01:17	AC	No
213	2020-10-01 02:01:13	AC	Yes
213	2020-10-01 03:01:13	DC	Yes
213	2020-10-01 04:01:13	GS	No

[Kenda]

Hi @svergtanya 

I have just a few clarifying questions to make sure I answer what you're looking for

    1. Is the Correct field the one you are trying to automatically generate here?

    2. Can you explain why records 4 and 7 are No?

[svergtanya]

Hi @Kenda 

1) Yes, I would like to generate the field "Correct"

2) I have a process, where are rules, which activities should be followed by which. For example, 'AC should be followed by DC and don't be repeated later after next activity.

If to look at them aggregated, so input can look like this 'AC, AC, DC, GC, FC, HF, TS, OP, DC, AC, ML'

and pattern should be 'AC, DC, FC, OP, TS, ML'

So, then if to color input, green - correct and red - wrong.

'AC, AC, DC, GC, FC, HF, TS, OP, DC, AC, ML'

[Kenda]

Hello @svergtanya 

I'm still not sure I have the entire picture, but I will attempt to provide you with something.

First try sorting your data by ID then timestamp, both ascending. Then use a Multi-Row formula tool to create a new string field with the values for rows that don't exist set to the closes valid row, grouping by ID, and with this expression:

iif([Row-1:Activity]="AC", 
	iif([Activity]in("AC", "DC", "FC", "OP", "TS", "ML"), 
		"Yes", "No"),
iif([Row-1:Activity]="DC", 
	iif([Activity]in("DC", "FC", "OP", "TS", "ML"), 
		"Yes", "No"),
null()))
	

This should be the basic logic that you can build from. Basically it is saying if the previous row was AC, then if this row's activity is listed in row 2 above, then say Yes. If the previous row was AC but this row is not in that list, say No. Then you just filter through all possible combinations of values for the previous row and allowed values for the current row.

Hope this helps!

[PhilipMannering · Accepted solution]

Hi @svergtanya 

Maybe you can adapt something from this,

[svergtanya]

@Kenda thank you for the recommendation. I'm trying to run your expression with iif, but somehow it always gives  'NULL'.