Get number of holiday based upon array input based on LinQ, Below Query working working first time but not second time

Help Activities
, ,
1

I am getting input as {“23/01”,“23/03”, " 23/04"}

image
image588×680 10.8 KB

Based upon this input I have to find the count of holidays in the below Calendar
image
Like in this scenario, Once I will iterate through Array {“23/01”,“23/03”, " 23/04"}
for
23/01 - If we look at the PP calendar ( 1st screenshot) we will get the from date 12/24/22 i.e. 24th Dec 2022 and to date 01/08/23 i.e. 8th Jan 2023. now we have to search for this date in the holiday calendar. I should get count 2 (one holiday on 25th Dec 2022 and another holiday on 1st Jan 2023.
Similarly for 23/03 - I should get a count of 0 ( start date 23rd Jan 2023 to 05th Feb 2023)
Similarly for 23/04 - I should get count 1 ( There is only one holiday on 13th Feb 2023 ( start date 06 Feb 2023 to 19th Feb 2023)

All the dates in Excel are in MM/dd/yyyy.

DtHoliday.AsEnumerable.Where(function(x) CDate(x(0).ToString) > DtCalendar.AsEnumerable.Where(function(y) y(0).ToString.Equals(currentItem)).Select(function(y) Cdate(y(1).ToString)).First AndAlso CDate(x(“Holiday”).ToString) < DtCalendar.AsEnumerable.Where(function(y) y(0).ToString.Equals(currentItem)).Select(function(y) Cdate(y(3).ToString)).First).Select(function(x) x(0).ToString).ToArray()

Above query is working fine first time ( without loop) but second time it’s giving message " No value in Sequence" and result is Null.
If we run individually, every time it will work fine, even while doing testing I created a small flow and it’s running fine in loop also but while implementing in project ( which is having huge workflow, it’s giving error while trying to fetch value second time.
I also tried to create separate workflow for this, but that also doesn’t help and getting same error.

I have attached both Excel also. Any experts please help.
PP Calender.xlsx (10.8 KB)
Holiday Calendar.xlsx (28.3 KB)

@Anil_G

2

One of many options

we can create the series of an interval e.g. For 23/04 by
grafik

And using an inner join / match / lookup / set operations approach to get the corresponding holidays

For sure we can concat multiple inputs like {“23/01”,“23/03”, " 23/04"}
to search the corresponding start date and generating the corresponding interval

3

Another strategy could be to transform the holiday list into lookup Dictionary / DT

  • Key: date, Value: XX/YY Marker

So we could later filter the dictionary/DT on all values/Marker Col contained within the input array

4

@ppr
Thanks a lot for response, sorry to say I but didn’t understand.

Holiday.xaml (9.6 KB)
This is my sample workflow for testing. How to implement your logic into this ?

5

For starter help we merged some proposed ideas

PP_Calendar DT after readrange

grafik
grafik487×506 49.8 KB

dictLKMarks | Dictionary(Of String, String()) =

(From d In dtData 
Let dp = DateTime.ParseExact(d(1).toString.Trim,"MM.dd.yy",System.Globalization.CultureInfo.InvariantCulture) 
Let dl = Enumerable.Range(0,15).Select(Function (x) dp.AddDays(x).toString("M/d/yyyy")).ToArray
Select t = Tuple.Create(d(0).toString.Trim, dl)).toDictionary(Function (t) t.Item1, Function (t)  t.Item2)

We can create an array of all datesm defined by the input
myFilter = {"23/01","23/03","23/04"}

AllValidDates | String Array =
myFilter.SelectMany(Function (x) dictLKMarks(x)).toArray

And can now filter the Holiday_DT by

arrHolidays | String Array =

(From d in dtHoliday.AsEnumerable
Select dh = d(0).toString.Trim
Where AllValidDates.Contains(dh)
Select v=dh).toArray

Kindly note:

  • there are many variations / options on it, so feel free to combine it as you want to
6

We would check the Datatable as it is leading when defining the formats

DT_Calendar:
grafik

DT_Holiday:
grafik

So we cas see that the formats are different

Above solution suggestion can be adapted by:

bring the format M/d/yyyy exact to the format from the dt_Calendar

Checks and prototypes can be done by:
Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - News / Tutorials - UiPath Community Forum

And also:
:ambulance: :sos: [FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues - News / Tutorials - UiPath Community Forum

7

Can you share the .xmal please?

8

was done within RnD and immediate panel

9

if possible please share , this is will be really to helpful for me.