Dear All, The situation is I have some transaction data which has transaction time, location, and various other columns. on the other hand I have another excel where the data is in the format below: Start Time End TimeLocation25-04-2020 06:00:4225-04-2020 08:03:30XYZ27-04-2020 16:01:0128-04-2020 00:05:56ABC I need output like below, the total number of transactions between start and end. The excel has 57000 count so cannot do manually. Start Time End TimeLocationCount25-04-2020 06:00:4225-04-2020 08:03:30XYZ300027-04-2020 16:01:0128-04-2020 00:05:56ABC2456

Filter based on columns in one excel and get count of transactions

Dear All,

The situation is I have some transaction data which has transaction time, location, and various other columns.

on the other hand I have another excel where the data is in the format below:

Start Time	End Time	Location
25-04-2020 06:00:42	25-04-2020 08:03:30	XYZ
27-04-2020 16:01:01	28-04-2020 00:05:56	ABC

I need output like below, the total number of transactions between start and end. The excel has 57000 count so cannot do manually.

Start Time	End Time	Location	Count
25-04-2020 06:00:42	25-04-2020 08:03:30	XYZ	3000
27-04-2020 16:01:01	28-04-2020 00:05:56	ABC	2456

Workflow

Accepted answers

RolandSchubert

Hi @Mysa ,

with Alteryx you shouldn't consider to do it manually ...

Using a Join tool to join ranges to transaction data and DateTime functions to compare should help. I've attached a sample workflow.

Let me know if it works for you.

Best,

Roland

FilterOnPeriod.yxmd

All comments

grazitti_sapna

Hi @Mysa , will it be possible for you to provide a sample dataset?In case you cannot share the dataset give this a try.Try using this configuration and let me know if this helps.

Input-

Output-

Thanks.

RolandSchubert

Hi @Mysa ,

with Alteryx you shouldn't consider to do it manually ...

Using a Join tool to join ranges to transaction data and DateTime functions to compare should help. I've attached a sample workflow.

Let me know if it works for you.

Best,

Roland

FilterOnPeriod.yxmd

Mysa

Thank a lot @RolandSchubert! that is what exactly I wanted 🙂

@grazziti_sapna, thanks for the reply. Apologies if my question was not clear.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703