Hi Team,
I have a collection where the Date column is my key identifier for duplicate values. There can be multiple matches for the same date.
So basically what I need is any matches found for the Date column I need to keep the latest row based on the [ID] date & remove the duplicates. This is to be done across the whole data set for all the dates. If there are no duplicates found for a specific date then no need to delete the row.
Any help is greatly appreciated.
Thanks
It works for all case for me. See the code below where there are 3 same dates with the last 2 characters as 01, 06 and 12 and the code only returns the 12 record, ie
Add this code to a button and test
ClearCollect(
colMatches,
{VALUE: 111, DATE: Date( 2021, 9, 28), ID: "2021-09-28"},
{VALUE: 121, DATE: Date( 2022, 5, 26), ID: "2022-05-26"},
{VALUE: 121, DATE: Date( 2022, 6, 1), ID: "2022-06-01"},
{VALUE: 121, DATE: Date( 2022, 6, 1), ID: "2022-06-06"},
{VALUE: 121, DATE: Date( 2022, 6, 1), ID: "2022-06-12"},
{VALUE: 117, DATE: Date( 2022, 2, 4), ID: "2022-02-04"},
{VALUE: 117, DATE: Date( 2022, 2, 4), ID: "2022-02-10"}
);
RemoveIf(
colMatches,
!(ID in
ForAll(
colMatches As _data,
If(
CountRows( Filter(colMatches, DATE = _data.DATE)) = 1 ||
Value( Right( _data.ID,2)) = Value( Right( First(Sort( Filter(colMatches, DATE = _data.DATE), ID, SortOrder.Descending)).ID, 2)),
{
VALUE: _data.VALUE,
DATE: _data.DATE,
ID: _data.ID
}
)
).ID
)
)
The only time this code won't work is if you get more than 99 records with the same date (ie last 2 characters are 99)
Yes although wont work for all scenarios. Example my only concern is taking the last 2 digits. Where it might be 2022-01-01 & 2021-16-12. The code will take the wrong value.
So is it working now? If so, please mark a solution for anyone else who may have a similar problem to solve.
Cheers
All good fixed it 🙂
Just my only concern is taking the last 2 digits. Where it might be 2022-01-01 & 2021-16-12. The code will take the wrong value
Did you try my code? I tested it on a small collection of data and it worked for me.
So both are text although should be referred to as dates. What I have done is sorted the collection by Date column then by ID column. So basically should be in the correct order where we should keep the first line which is a match and can delete the duplicates after it.
Any help with this ?
Thanks again @EddieE
Assuming that ID is of type Text and the Max of the last 2 characters of ID describes the record you are looking for
eg in your example, 06 beats 02, then try this
RemoveIf(
yourCollectionName,
!(ID in
ForAll(
yourCollectionName As _data,
If(
CountRows( Filter(yourCollectionName, DATE = _data.DATE)) = 1 ||
Value( Right( _data.ID,2)) = Value( Right( First(Sort( Filter(yourCollectionName, DATE = _data.DATE), ID, SortOrder.Descending)).ID, 2)),
{
VALUE: _data.VALUE,
DATE: _data.DATE,
ID: _data.ID
}
)
).ID
)
)