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

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

Status.yxmd

All comments

binu_acs

@RoxanneR What are the expected results?

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.

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

Also attached the workflow for you to look at

Status.yxmd

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

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.

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:

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?

Happy New Year!

So, i've had another look at this for you and please find attached a revised workflow that should work given the additional detail above.

Firstly the Tile tool will place each True instance into its own 'bucket', and sequentially do this for each ID (if you add in other ID's simply tick these on the Group By Columns box) - this identifies the unique runs of True and False

We filter out the False statuses, and summerise on ID and Tile_Num (again if you add in other ID's simply add this in as a group by option) to find the first and last timestamps for each group of True statuses

We then calculate the difference in days, join the data back to the original and sort. The difference in days will be attached to the last True instance of that particular group.

If there is only one True instance, it returns 0

Hope this is what you're looking for!

Status.yxmd

Hi @davidskaife ,

happy New Year to you as well.

I implemented your solution and one step didn’t work for me- as I join values on the with the join tool as suggested I get the Timestamp value which is the Min_timestamp instead of Max_timestamp. I don’t really understand how it’s possible since I configured everything as per your example. Should I add it manually somewhere?

thanks,

Roxanne

Do you mind sharing a screenshot of your Join tool settings?

Hi, the solution worked for me after all. May I ask why don't we group by Timestamp in Tile tool as well, only by ID(s)?

Thanks!