Multi-row formula issue

[hamzam]

Hi all,

New to altteryx!

In my workflow, I convert date (string) to DateTime using the DateTime tool. Then,

I have the following multi-row formula:

However, this is what I get for the new column:

As you can see, this is not behaving as expected; e.g. the difference between the third and fourth row should be 6 minutes = 360 seconds, but it reports 60. Could you please let me know what could be wrong?

The purpose of the new column is to group the rows together that are close to each other (i.e. that dont differ by more than 60 seconds)

Thanks

[bmcclelland46]

I think there are a couple things you can try. If this data is sorted, you should never have a negative time popping up- try flipping the dt1 and dt2 variables (Row+1:x,x,”seconds”) and make sure that the data type is large enough to hold the number of seconds- try double or int64. If you are still getting a weird answer, try alternating the rows 1,2,1,2 and create a cross tab so you can just use a typical formula tool and you can always pivot the fields back. Let me know if that helps!

[hamzam]

Hi,

the data is not sorted. I changed it from int32 to int64, but same results. Could you expand on what you mean by "alternating the rows 1,2,1,2..."?

thank you

[bmcclelland46]

Could you please upload your workflow or input data (you can just include the date field if the other information is sensitive) and I can help you out.

Little screenshot of the pivot I was talking about.

[hamzam]

Hi,

sorry about the late response.

here is a sample of the data attached. Please note that the ID is not always the same. As a next step, we would also want it to group by the ID. So, e.g. first group by ID then finding similar rows (with time difference less than 2 minutes) makes more sense.

Your earlier comment reminds me that we should sort the data after grouping by the ID I believe.

Thanks!

[bmcclelland46 · Accepted solution]

Good news! Think I have what you are looking for. Please see the attached workflows (attached a workflow zip and regular workflow in case you had trouble with either.) Let me know if you have any questions and please mark this as solved if I helped. 

[hamzam]

Awesome, thank you!

If you don't mind, how would I be able to create the groups from this;

e.g. up until I see a 'Yes', I would like to group together those rows. Then, for the first 'Yes' until the next 'Yes' (or until the end of the data), another group would be formed,... We can name these groups 0,....,n, where n is the final group.

Thanks again.