I am working with a dataset containing 22,232,726 entries collected between 2008 and 2021. Because original entries can not be deleted from the database, a new entry must be created with the same ID to update an observation.
I want to remove all repeated IDs leaving only the latest entry per ID for my analysis.
I used the following Level of Detail function in Tableau to achieve this:
{FIXED [ID]: MAX([Date])} = [Date]
The function returns a total of 17,980,416 entries. However, when I run a distinct count COUNTD([ID]) before and after applying the LOD filter, I get 17,899,956 distinct IDs. Why is my LOD function returning an extra 80,460 repeated IDs to the result?
FYI, there are no Nulls in the ID nor the Date columns. So there can be repeated dates for the same ID, but I expected Tableau to keep only one of them in the results. How can I remove these extra repeated entries or fix this counting problem?
I eventually found a solution to the problem by using a Row_ID field as the criterium for selecting one of the records with an identical ID and Date. I used 2 LOD calcs as filters.
The first filter kept all unique IDs with the latest Date, including some repeated IDs with the same latest date.
1:{FIXED [ID]: MAX([Date])} = [Date]
The second filter took the repeated records with identical ID and Date and kept only the one with the last Row_ID.
2:{FIXED [ID],[Date]: MAX([Row_ID])}=[Row_ID]
The original dataset doesn't have a Row_ID variable, so I had to create it by using Pandas in Python by adding index and index_label parameters:
df.to_csv("my-file-name.csv", index=True, index_label='Row_ID')