Hi,
Need help in finding missing dates.
At the moment I have two collections.
1st collection called DATE_INFO which has say the last 4 years of dates. Each line represents a date.
2nd collection is Read_Dates where each line will have different dates although in date order.
Example Read_Dates
Row 1: 2023-10-15
Row 2: 2023-08-10
Row 3: 2023-06-05
Row 4: 2022-12-21
Now in the column of Days_Missing in my Read_Dates collection i need to see if any days are missing against the DATE_INFO collection.
Example Read_Dates for row 1 will check the range from row 2 & 1 ( Start Date 2023-08-10 to End Date 2023-10-15) against DATE_INFO to see if all dates in the range are there. If not I need to update the Days_Missing with the number of dates missing.
This will need to be done on each row in the Read_Dates collection where the next row is the start date and the current row is the end date.
I've tried a few things although can't seem to get the correct outcome.
Any help is greatly appreciated.
Thanks
That's a bummer - What if you count the number of rows in the Date_Read collection and set a variable to that value (var_Size). Use the Index function in a ForAll to make a new collection, var_Size will make sure you don't try to go past the end of the Date_Read collection. In that ForAll read the current Item and the next item in the collection (with Index you can set the item to read as one more than the current item). Using that technique you can then Collect into a new collection with the current value and the next value in the same record. Then you can use DateDiff to get the time between those values.
Thank you although I get an error.
Done some research and you cant use the set function within ForAll 🙁
The function can not be invoked within a ForAll.
I'm not where I can test this, but it should be close
Not sure what you need to do with the data, but to get the answers you will use ForAll to go thru the list of Read_Dates. Code would be similar to this - please excuse syntax errors - as I say I can't test it now.
Set(var_1, First(Read_Dates).Value;
ForAll(Read_Dates,
Set(var_2, ThisRecord.Value);
Set(var_DaysBetween, DateDiff(var_1,var_2) );
Patch(Somewhere, var_DaysBetween);
Set(var_1, var_2)
)
I hope this rough outline gets you where you need to go.