Use Table Calculations to Get Most Recent Line Item When User Has Duplicate Rows (Non-subquery version)

[Eric_Lyons]

Knowledge Drop

Last tested: Mar 19, 2020
 

If you have line items in a table with duplicate ids, but only wants the most recent items or highest items many times you will try to use a SQL subquery to SELECT MAX(DATE) for the given parameter, but you can create this with table calculations in an explore.

First, sort the explore by the ID. Then, apply a secondary sort to the date column (descending).

Then, we can create a table calculation that checks if the value below the id is a duplicate.

Then, we check the date column for duplicates.



 

This content is subject to limited support.                

[WyattNW]

Im unable to see the PNG youve added can you please try and add them again, would be very helpful

[Mahesh_babu]

How to write calculation for create a field that shows the previous week's count next to each week's count

2. Create a field that computes the difference between each week's count and it's previous week