Data filter in subset

[davide moltrasio 2]

Hi, i want to filter my data with the max datetime for each date and foreach value of column a

This is my start table

Column-A Column-B

COD1 14/02/2019 15:10:03

COD1 14/02/2019 15:21:25

COD1 14/02/2019 18:05:20

COD1 15/02/2019 15:10:03

COD1 15/02/2019 15:21:25

COD1 15/02/2019 18:05:20

COD2 14/02/2019 15:10:03

COD2 14/02/2019 15:21:25

COD2 14/02/2019 18:05:20

COD2 15/02/2019 15:10:03

COD2 15/02/2019 15:21:25

COD2 15/02/2019 18:05:20

[...] [...]

 

This is what I want:

Column-A Column-B

COD1 14/02/2019 18:05:20

COD1 15/02/2019 18:05:20

COD2 14/02/2019 18:05:20

COD2 15/02/2019 18:05:20

[...] [...]

How can I write a custom expression

Thanks

[Kirsten Smith (she/her)]

I kind of did this the long way, but this should give you a starting point for your data.

 

First, I created a Date column that is just the Date part of your DateTime column called [singleDate]. Then I used this expression to create a new column in the data set:

 

case when [Column-B]=Max([Column-B]) OVER ([singledate]) then "Yes" else "No" end

 

This creates a column of "Yes" and "No" values, then you can just filter out the "No" values.