eddy ng Posted April 21, 2020 Share Posted April 21, 2020 Details of the table are in the attached, [column JOB] [time stamp] [time sequence] [tonnes] A 1/1/2020 1 5000 A 1/1/2020 2 6000 B C C C [outcome i want from the calculated column)= Max [time stamp] and Max[time sequence]over [column job], return [tonnes]= "6000" Link to comment Share on other sites More sharing options...
Shashank Gurnalkar Posted April 22, 2020 Share Posted April 22, 2020 Try this expression. I am getting the expected output with your sample data. If(([time stamp]=Max([time stamp]) Over ([column JOB])) and ([time sequence]=Max([time sequence]) Over ([column JOB])),[tonnes]) Link to comment Share on other sites More sharing options...
eddy ng Posted April 23, 2020 Author Share Posted April 23, 2020 Sorry doesnt work, Because, max time stamp over job will not always intersect with over all max time sequence, hence coming up with null for everything. We want to have max timestamp over job, and then max time sequence of the max timestamp- (if that makes sense).. example: max timestamp (23/04/2020 ) will result in 3 rows (that has the same timestamp) Within the 3 rows there will be a max time sequence (listed 1,2 &3) I want time sequence= 3 that are on rows with 23/04/2020. If you try to get max of both, there will be higher sequence on other dates- hence coming up with null. Link to comment Share on other sites More sharing options...
Shashank Gurnalkar Posted April 23, 2020 Share Posted April 23, 2020 Sorry, I misread it. This can be achieved using nested Case statements like: Case [time stamp] When Max([time stamp]) OVER ([column JOB]) then Case [time sequence] When Max([time sequence]) OVER ([column JOB]) then [tonnes] End End See the test column below: Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now