Group transactions that occurred within a 30 second time frame

Hello - I'm trying to group transactions that occurred within 30 seconds of each other to aggregate transaction amounts. I've attached sample data to show what my input looks like and what I would like my output to look like. The colors don't mean anything and aren't needed, I just added it to help visualize the transactions that would be grouped together. Any help would be much appreciated. Thanks.

Sample.xlsx

Tips and Tricks

Help

Expression

Accepted answers

davidskaife

Hi @elclark

It's due to the dates; in both instances the second date time is before the first, so the results are negative values and triggering as true:

One way of rectifying is to tweak the formula to this, essentially saying it must also be at or above 0:

IF datetimediff([DateTime_Out],[Row-1:DateTime_Out],"sec")<31 AND datetimediff([DateTime_Out],[Row-1:DateTime_Out],"sec")>=0
THEN 1
ELSE 0
ENDIF

All comments

ShankerV

Hi @elclark

Please find the expected output.

From the above output, if you need to merge cells like below in the excel sheet.

Please add the below steps to my existing workflow to merge the cells on the output.

https://community.alteryx.com/t5/Engine-Works/How-to-Create-Merged-Cells-in-Reports-Part-1/bc-p/975775#M3195

The above article created by @atcodedog05 helps to merge the cells.

Many thanks

Shanker V

Group transactions that occurred within a 30 second time frame.yxmd

elclark

Hello - thank you for the help, so I'm running into an issue where the multi-row formula is not accurately assigning a 1 or 0 in a couple of instances. I've attached my workflow and you can see between records 5/6 and 22/23, there is more than a 30-second time difference but it still assigned a 1 instead of 0. Do you know what could be causing this? Thanks.

Group transactions that occurred within a 30 second time framev2.yxmd

davidskaife

Hi @elclark

It's due to the dates; in both instances the second date time is before the first, so the results are negative values and triggering as true:

One way of rectifying is to tweak the formula to this, essentially saying it must also be at or above 0:

IF datetimediff([DateTime_Out],[Row-1:DateTime_Out],"sec")<31 AND datetimediff([DateTime_Out],[Row-1:DateTime_Out],"sec")>=0
THEN 1
ELSE 0
ENDIF

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703