Temporal buffer

[sdprice]

Hi all.

This sounds so simple but I can't think how to approach it. Basically, I have a data set with timestamp records and I want to count uniques in another field that are within +/- 60 seconds of each individual record. I think my issue is not knowing how to compare an individual record's value against all values in the field.

Thanks.

[Inactive User]

Try using a DateTimeDiff function and filter the stream to have anything with a difference of 60 seconds between the two date fields you are comparing. Then you will have your dataset that is within 60 seconds to select uniques from, using unique tools or other mechanisms.

[sdprice]

> Try using a DateTimeDiff function and filter the stream to have anything with a difference of 60 seconds between the two date fields you are comparing.

This first part is actually where I'm stuck. I'm looking to compare individual timestamps against all timestamps in the set. The DateTimeDiff function is easy and I get the concept of what I need to do, just not how to apply it.

If it helps, my aim is to find cases where multiple individuals were active around the same time in a temporally-sparse data set.

[BenMoss]

What you need here is the generate rows tool.

This will allow you to create a new record for each second in your buffer.

Once you have this dataset you can perform a join to find the comparible records from within the buffer.

Ben

[BenMoss · Accepted solution]

Here is a working example of what I mean and what I believe you are looking to achieve.

Ben

[sdprice]

This did the trick. It's surprisingly complex, but it makes sense once it's run. Thanks, Ben.

[BenMoss]

No problem!

You may also wish to check out this macro: https://gallery.alteryx.com/#!app/Advanced-Join/547f8df96ac90f0f2ca5e439

Which is mentioned in the 2nd post on this knowledge base article: https://community.alteryx.com/t5/Alteryx-Knowledge-Base/How-to-Join-on-a-Date-Range/ta-p/29332

I believe in the background it's essentially doing the same thing!

Ben