Dear Forum Members: Please see my data below: ProductGroup ItemDateTimeValueQtyGrp-01Item00118/03/2021 06:12105Grp-01Item00118/03/2021 06:1398Grp-01Item00118/03/2021 06:1482Grp-01Item00118/03/2021 06:15103Grp-01Item00118/03/2021 06:1621Grp-01Item00118/03/2021 06:2032Grp-01Item00218/03/2021 04:12105Grp-01Item00218/03/2021 06:0498Grp-01Item00218/03/2021 05:1482Grp-01Item00218/03/2021 06:11103Grp-01Item00218/03/2021 06:2221Grp-01Item00218/03/2021 06:2432Grp-01Item00318/03/2021 04:12105Grp-01Item00318/03/2021 06:0498Grp-01Item00318/03/2021 05:1482Grp-01Item00318/03/2021 06:11103Grp-01Item00318/03/2021 06:1921Grp-01Item00318/03/2021 06:2132Grp-01Item00418/03/2021 03:12105Grp-01Item00418/03/2021 03:3498Grp-01Item00418/03/2021 04:1182Grp-01Item00418/03/2021 05:41103Grp-01Item00418/03/2021 06:0121Grp-01Item00418/03/2021 06:2432 Green and bold rows are the desired output for further analysis, below desired output from the above table: ProductGroup ItemDateTimeValueQtyGrp-01Item00118/03/2021 06:2032Grp-01Item00218/03/2021 06:2221Grp-01Item00318/03/2021 06:2132Grp-01Item00418/03/2021 06:2432 I have groups and each group contains 2 or 4 items, Each group items has more than one row for each day, every row is timestamped. I need to pick up 2/4 items from each group with the closest datetime matched, if they matched more than 1 as well then all the entries (possibly group by Product Group, Item, Date) but the example above is showing one day entries for the items in a group. The above is all one dataset. Do You think this is possible? thank You in advance.

Closest datetime match within a group for multiple items with multiple entries

Dear Forum Members:

Please see my data below:

ProductGroup	Item	DateTime	Value	Qty
Grp-01	Item001	18/03/2021 06:12	10	5
Grp-01	Item001	18/03/2021 06:13	9	8
Grp-01	Item001	18/03/2021 06:14	8	2
Grp-01	Item001	18/03/2021 06:15	10	3
Grp-01	Item001	18/03/2021 06:16	2	1
Grp-01	Item001	18/03/2021 06:20	3	2
Grp-01	Item002	18/03/2021 04:12	10	5
Grp-01	Item002	18/03/2021 06:04	9	8
Grp-01	Item002	18/03/2021 05:14	8	2
Grp-01	Item002	18/03/2021 06:11	10	3
Grp-01	Item002	18/03/2021 06:22	2	1
Grp-01	Item002	18/03/2021 06:24	3	2
Grp-01	Item003	18/03/2021 04:12	10	5
Grp-01	Item003	18/03/2021 06:04	9	8
Grp-01	Item003	18/03/2021 05:14	8	2
Grp-01	Item003	18/03/2021 06:11	10	3
Grp-01	Item003	18/03/2021 06:19	2	1
Grp-01	Item003	18/03/2021 06:21	3	2
Grp-01	Item004	18/03/2021 03:12	10	5
Grp-01	Item004	18/03/2021 03:34	9	8
Grp-01	Item004	18/03/2021 04:11	8	2
Grp-01	Item004	18/03/2021 05:41	10	3
Grp-01	Item004	18/03/2021 06:01	2	1
Grp-01	Item004	18/03/2021 06:24	3	2

Green and bold rows are the desired output for further analysis, below desired output from the above table:

ProductGroup	Item	DateTime	Value	Qty
Grp-01	Item001	18/03/2021 06:20	3	2
Grp-01	Item002	18/03/2021 06:22	2	1
Grp-01	Item003	18/03/2021 06:21	3	2
Grp-01	Item004	18/03/2021 06:24	3	2

I have groups and each group contains 2 or 4 items, Each group items has more than one row for each day, every row is timestamped.

I need to pick up 2/4 items from each group with the closest datetime matched, if they matched more than 1 as well then all the entries (possibly group by Product Group, Item, Date) but the example above is showing one day entries for the items in a group.

The above is all one dataset.

Do You think this is possible?

thank You in advance.

Datasets

Date Time

Accepted answers

OllieClarke

Hi @Data-Nuker I just tried my solution on your original data set, and you get 2 Item002 (because there were two records the same difference in time).

If you don't mind which record is actually returned then I'd suggest a summarise tool at the end which takes the first record per ProductGroup and Item. I've attached the updated flow here

smallest timediff.yxmd

All comments

AngelosPachis

Hi @Data-Nuker ,

If you convert the Datetime field into a format that Alteryx can understand as a date, then you can use a summarize tool to find the most recent timestamp for each product group and Item, and only keep those records.

Is this what you are looking for?

Cheers

Angelos

example.yxmd

Data-Nuker

Hi @AngelosPachis

Thank You very much for your prompt response, amazingly quick response, many thanks.

I did that but Max can have two issues, I should have clarify in my original post, sorry for that:

1. it will have max date selected for each group whereas I required it for each day, this is not an issue, I can have 2 columns one for date and other for datetime and then will group by.

2. This is the most important issue using max for me, suppose the time is not closest to others for example below:

ProductGroup	Item	DateTime	Value	Qty
Grp-01	Item001	18/03/2021 05:12	10	5
Grp-01	Item001	18/03/2021 05:19	9	8
Grp-01	Item001	18/03/2021 06:14	8	2
Grp-01	Item001	18/03/2021 06:15	10	3
Grp-01	Item001	18/03/2021 06:16	2	1
Grp-01	Item001	18/03/2021 06:20	3	2
Grp-01	Item002	18/03/2021 04:12	10	5
Grp-01	Item002	18/03/2021 04:20	9	8
Grp-01	Item002	18/03/2021 04:34	8	2
Grp-01	Item002	18/03/2021 04:44	10	3
Grp-01	Item002	18/03/2021 05:20	2	1
Grp-01	Item002	18/03/2021 06:21	3	2
Grp-01	Item003	18/03/2021 04:12	10	5
Grp-01	Item003	18/03/2021 06:04	9	8
Grp-01	Item003	18/03/2021 05:14	8	2
Grp-01	Item003	18/03/2021 06:11	10	3
Grp-01	Item003	18/03/2021 06:19	2	1
Grp-01	Item003	18/03/2021 06:21	3	2
Grp-01	Item004	18/03/2021 03:12	10	5
Grp-01	Item004	18/03/2021 03:34	9	8
Grp-01	Item004	18/03/2021 04:11	8	2
Grp-01	Item004	18/03/2021 05:19	10	3
Grp-01	Item004	18/03/2021 06:01	2	1
Grp-01	Item004	18/03/2021 06:29	3	2

From the above table desired output now:

ProductGroup	Item	DateTime	Value	Qty
Grp-01	Item001	18/03/2021 05:19	9	8
Grp-01	Item002	18/03/2021 05:20	2	1
Grp-01	Item003	18/03/2021 05:14	8	2
Grp-01	Item004	18/03/2021 05:19	10	3

There is a possibility that an item in a group have recorded more than 1 time but have a difference in hours from others probably an hour or two hours, so using max will not give me my required results.

I hope this makes more clear?

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335