Calculate the first instance the status was true for the specific ID taking into account

[RoxanneR]

Hi, please help! I have input files containing data for specific month and containing information about the ID, Status which can be true or false and time period containing the months. Status is changing for the IDs throughout time. I need to calculate the first instance the status was true for the specific ID taking into account 3 months time period and display it.

Example:

| ID | Status | Timestamp |

|----------|--------|-------------------|

| ID_1 | True | 2023-01-05 08:00 |

| ID_1 | False | 2023-02-10 15:30 |

| ID_1 | True | 2023-03-20 12:45 |

| ID_2 | False | 2023-01-02 11:15 |

| ID_2 | True | 2023-02-18 09:45 |

| ID_2 | True | 2023-03-25 14:20 |

| ID_3 | True | 2023-01-08 13:30 |

| ID_3 | True | 2023-02-15 17:10 |

| ID_3 | False | 2023-03-10 10:00 |

Next step would be to display the time status was True before it turned to false, it should be displayed in the new column

[binuacs]

@RoxanneR What are the expected results?

[RoxanneR]

Hi. the expected result is the new column containing a flag that would indicate the first instance of status being 'True' within the time period - lets say we have data for March, April, May and if status was first time True in April then the flag should be displayed. 
Next expected result would be to calculate for how long the Status was True within those months.

PS: I cant do it manually for each ID as I have over 10K of unique IDs.

[davidskaife · Accepted solution]

Hi @RoxanneR 

Here is an attempt at what you're looking for, based on my understanding. Let me know if you need any tweaks.

First section of the workflow identifies the first instance of a True status for that ID

Second section identifies how many times the True status exists in sequence from that point, and then calculates the length of time in days between those two dates. I've gone with days as you haven't specified what time difference you require, but can be changes to Hours, Months etc

Also attached the workflow for you to look at

[RoxanneR]

Hi David, thanks for help.

I tried to apply the workflow to my data- the idea is good! 

However, I have the data divided by months in different input files - therefore I guess this way of applying the flag isn't applicable - as it flags the only month the file has.

Would Union the files before applying the Sample and Formula flag=1 solve this? 

Thanks in advance

[davidskaife]

Hi @RoxanneR 

If you have multiple files, one for each month by the sounds of it, then you’ll need to bring them all into Alteryx and union prior to applying my suggested solution. 

note you’ll have to sort the data as well so they are in ID and Time order for it to work correctly.

[RoxanneR]

Thank you, David.

Just realized I need to add up one more variable here- it should be the combination of ID and Regional_ID - 

so ID can repeat within different Regional_IDs with different statuses. Would you have an idea how to approach this change?

Many thanks

[davidskaife]

Hey @RoxanneR 

No problem at all!

If you have an additional ID, then any tool that is grouped by/included in a join, sorted by an ID column will need that ticking as well; so that's the Sample tool, the Join tool, the Sort tools, the Multi-Row Formula tools - see my screenshot below:

[RoxanneR]

Hi @davidskaife , Thanks and sorry for the long reply.

I implemented the solution of yours and I have a question - does it count all the instances of a True status for the assigned id regardless if there was a False status in between? 

[davidskaife]

Hey @RoxanneR 

I’m on mobile so apologies I cannot check it for sure, but, it will flag true for only the first instance within the ID. 

do you need extra clauses adding such as month? I ran on the assumption each month was one record…