Duplicates id

hello good afternoon

I would like to ‘clean’ my quicksight table of duplicates, because when I filter by month, I get all the records associated with each token, but I only want the last value, or value associated with the date, here’s an example of my analysis, max date is the maxdate of next_billing_Date
token maxdate(last date registred to each token) next_billing_date(field that is causing the error)

a1 30/12/2023 30/12/2023
b2 28/12/2023 28/12/2023
b2 25/11/2023 28/12/2023
d1 12/12/2023 12/12/2023
d1 25/10/2023 12/12/2023
d1 2/06/2023 12/12/2023

I only want 1 value of token when i filter, nos all of them, currently i filter using maxdate, hoy could i do that?

Hello @Juan, so you only want a single record for each token, am I correct? If so, you might want to use the maxOver function, which allows you to get the maxDate per token and should only return 1 row each.

maxDate = maxOver({next_billing_date}, [{token}], PRE_AGG)

Now you can use maxDate as your date field in the table with your token and it should return only a single value for each. I’ll mark this as a solution, but if you have any follow-up questions, please let me know.

hI @DylanM, I Have this field already(without PRE_AGG) maxDate = maxOver({next_billing_date}, [{token}], PRE_AGG), but the problem happens when i visualize with a table and use 7 fields in the groupby section, all duplicates appear instead of only 1 wich would be the logic value

Hello @Juan, I completely understand. If your group by fields are causing an issue here, then you can build one more calculation and fix this with a filter.

onlyMaxDate = ifelse({maxDate} = {next_billing_date}, {next_billing_date}, NULL)

Now replace maxDate with onlyMaxDate and add a filter on the visual for onlyMaxDate. To make this work on my end, I used a Date & time range filter, added a Rolling start date of Start of Previous Year, and an end date of Today (you can change this depending on the variance you receive from the maxDate function). Then you have to make sure you select Exclude nulls in the Null Options dropdown. I’ll paste a screenshot below for how I set this up. That should fix your problem!

1 Like

Thanks @DylanM this works like a charm!
The filter type needs to be date & time range or it could be relative dates?

1 Like

Hello @Juan, relative dates should work as well! As long as you have the option to exclude nulls on the filter, you can customize the date filtering option how you want. Thanks for following up and I am glad that it worked!

1 Like