You’re offline. This is a read only version of the page.
8
I have a collection with 3 columns, Location, Date and Value. I need to create another collection based on the existing collection, but if the date, at a location is within 3 days of a previous entry at the same location, I don't want to add it to the new collection.
Example:
Collection1: Location Date Value
New York 12/19/2020 5
New York 12/23/2020 3
New York 12/25/2020 2
L.A 12/19/2020 3
L.A 12/21/2020 3
L.A 12/27/2020 5
Desired Collection2: Location Date Value
New York 12/19/2020 5
New York 12/23/2020 3
L.A 12/19/2020 3
L.A 12/27/2020 5
As you can see, the entries from the first collection, that had an entry at the same location within 3 days prior, werent added to the second collection.
Is this possible, if so how can it be achieved?
Thanks
Hello @mrizz ,
I think something like this should work but I'm not sure it's the best..
Share
Report
All responses (
Answers (
Clear(Collection2);
ForAll(Collection1,
If(IsBlank(LookUp(Collection2, Location in Collection1[@Location] && Abs(DateDiff(Date,Collection1[@Date])<3))),
Collect(Collection2,ThisRecord))
)
Regards,
Akram
------------------------------------------------------------------------------
If this helped you, please consider giving it kudos and accept it as a solution. Thanks
