Max Record based on Date

[JDong]

Hello Team @atcodedog05 ,

Please refer to this sample dataset from my overall data

Last Modified By	LastModifiedDate	Company
Jack	09.12.2020 07:46	ABC Tech
Jack	11.12.2020 14:35	ABC Tech
Peter	21.12.2020 06:15	FDE Auto
Winson	30.10.2020 12:15	HIC Fenster
Blake	30.10.2020 12:15	IRT Iron
Winson	14.12.2020 10:11	HIC Fenster

I need to retreive one line of record and group the records based on max of modified date

So end result in this case would be 

Last Modified By	LastModifiedDate	Company
Jack	11.12.2020 14:35	ABC Tech
Peter	21.12.2020 06:15	FDE Auto
Blake	30.10.2020 12:15	IRT Iron
Winson	14.12.2020 10:11	HIC Fenster

Can you please advise with how this can be achieved in Alteryx.

Thanks

[Kenda]

Hi @JDong 

Try first sorting your data by Last Modified By then by LastModifiedDate. From there, use the sample tool to keep the last record, grouping by the Last Modified By field.

Note - you may have to edit your date field first so that it is in the Alteryx date format.

Hope this helps!

[JDong]

@Kenda 

Thanks this is helpful.

In my actual dataset I have derived close to 40 columns, so the grouping is not happening on all columns. Also the date now shows same for all rows of data.

Will it help if I add a summerize tool after the Sample tool ?

Thanks

[Kenda]

@JDong 

I'm not sure I understand exactly without seeing your data, but the Sample tool has a grouping functionality in it. Whichever fields you need to group by when selecting which rows to keep can be done within this tool. 

[JDong]

@Kenda 

Not all records are shown after grouping in Sample tool based on Name.

Would it work on grouping rest of the columns ?

Thanks

[Kenda]

@JDong  Right. Not all records are shown because we told the sample tool to only keep the last record for each unique value of the name field. If you group by additional fields, it will keep the last record for each unique combination of fields that you select. 

Take this data for example:  

Name	Group	Record
John	a	1
Kathy	a	2
Winston	a	3
Matt	a	4
Carol	b	5
Matt	b	6
John	a	7
Winston	b	8
Carol	b	9
Kathy	a	10

If you use the sample tool to keep the last record and only group by the Name field, you would get this result (one record per name):

Name	Group	Record
Carol	b	9
John	a	7
Kathy	a	10
Matt	b	6
Winston	b	8

If you use the sample tool to keep the last record and group by both Name and Group, this would be the result (one record per each unique Name & Group combination):

Name	Group	Record
Carol	b	9
John	a	7
Kathy	a	10
Matt	a	4
Matt	b	6
Winston	a	3
Winston	b	8

So it really just depends on what your data looks like and what your goal is. Hope that helps!

[JDong]

@Kenda 

Thanks !

This is a nice solution, but problem is I have 40 columns and on grouping on different columns I get different results.

Is there an easier way to solve this use case ?

[Emil_Kos]

Hi @JDong,

If you want to have one line per grouping you can use summarize tool and max function.

If this isn't something that you have been looking for please let me know:

The output:

Please mark my post as a solution if this was helpful!

[JDong]

@Emil_Kos 

Thanks !

If there are more than 1 record in same date but time is different, it does not get the max date.

Any way based on the date and time we can select a single max value ?

[Emil_Kos]

Hi @JDong,

It will show only 1 max value. I just tested it and it works. Please check the line 5 and 7.

And the output:

Alternatively, you can use the sample tool on the MAX date time in order to be sure.