How to extract data for last 7 consecutive days from an excel data table

Help Activities
, ,
1

I have an excel and I need to extract data for last 7 consecutive days and if data for any day is not available it should not be filtered. for example Today is 30th and from last 7 days data is available for 29th, 26th,25th, and 24th. the output should have data of 29th date only because consecutive 28th is not available so it should miss all other dates like 26,25,and 24.

Similarly if data is available for 29,28,27 also then it should pick data for all dates

2

Hey!

Try like this:

Now.AddDays(-7).ToString("dd-MM-yyyy")

Regards,
NaNi

3

this give data for all last 7 even if for 2nd last day is not available

4

Hi @faiqaqureshi90 ,

Could you provide us with the Input data in the form of Excel and also the expected output data for the same ?

We could understand the logic faster and provide you a solution at the earliest in this way.

5

Uploading: Copy of Report.xlsx…

6

please check the upload or upload again:
grafik
we cannot access the sample data

7
Credit Switch Update Date
Disabled 6/29/2022
Disabled 6/29/2022
Disabled 6/24/2022
Disabled 6/26/2022
Disabled 6/24/2022
Disabled 6/25/2022
Disabled 6/24/2022
Disabled 6/24/2022
Disabled 6/24/2022
Disabled 6/24/2022
Disabled 6/24/2022
Disabled 6/26/2022
Disabled 6/25/2022
Disabled 6/24/2022
Disabled 6/26/2022
Disabled 6/24/2022
Disabled 6/24/2022
Disabled 6/24/2022
Disabled 6/24/2022
Disabled 6/26/2022
Disabled 6/25/2022
8

there is some issue uploading the file here

9

we can check woth a Linq and can return a true/false if all consecutive days were present or not in the datatable

Ideas:

grafik
grafik974×69 26.2 KB

(From x in Enumerable.Range(-7,7).select(Function (x) now.AddDays(x).date)
Let chk = dtDataVar.AsEnumerable.Any(Function (d) CDate(d("Update Date").toString).Date = x.Date)
Select b=chk).All(Function (x) x)

Depending on how the date are present in the datatable we mabye will adapt the datretime parsing with some little steps

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

10

I want data to be extracted. like for the following table i need only row with date 29th.

Credit Switch Update Date
Disabled 6/29/2022
Disabled 6/26/2022
Disabled 6/25/2022
Disabled 6/26/2022

if data is as follows that is 29th and 28 is not present the output should contain rows with date 26,25 and 24

Credit Switch Update Date
Disabled 6/24/2022
Disabled 6/25/2022
Disabled 6/26/2022
Disabled 6/24/2022
Disabled 6/25/2022
11

as an alternate and less LINQ usage aprroach we can do following

arrDTDates = dtData.AsEnumerable.Select(Function (x) x("Update Date").toString.Trim).Distinct().toArray

arrCheckDates = Enumerable.Range(-7,7).select(Function (x) now.AddDays(x).toString("M/d/yyyy").toarray

isComplete = arrCheckDates.Except(arrDTDates).Count = 0
12

data for which ever date is present either its direct consecutive or it misses the last date and starts day before last day

13

Requirements

And

Let us summarize

Filter out the rows where the closest and subsequent date series of Today til -/ days is present, Right

Give us some little time, we will work on a solution approach

14

yes
plus it should not contain today’s data

15

Find one of many approaches with the strategy of calculating the filter list
Lets also ask one of my RPA Team members @kumar.varun2 for alternates or optimizations

Variables:

grafik
grafik859×226 53.1 KB

Sample Input data:
grafik

the check date we set to today 06/30/2022

First part of flow:
grafik

At first stage we fetch the present dates and calculate the range dates

arrRangeDates = Enumerable.Range(1,6).select(Function (x) now.AddDays( - x).Date).ToArray
arrDataDates =  dtData.AsEnumerable.Select(Function (x) CDate(x("Update Date").toString.Trim).date).Distinct().toArray

grafik

then we check which dates are already present within the range

arrFoundDates = arrDataDates.intersect(arrRangeDates).OrderByDescending(Function (x) x).ToArray

grafik

And finaly we check for the closesst consecutive days
grafik

arrFilterDates = arrFoundDates.Skip(1).TakeWhile(Function (x,i) arrFoundDates.First().AddDays(-1 - i) = x).Prepend(arrFoundDates.First()).toArray

as the found dates are ordered we do need the first date for sure and ommit it for the evaluation
Then we check for each date if it is the same date as the first date (we ommited)
therefore we are using -1 - the sequence loop counter (0 for first,1 for second …)
So we can detect the series and will prepend the ommited first element

On the last step we use the calculated arrFilterDates for filtering the origin datatable:

dtFiltered = 
(From  d In dtData.AsEnumerable
Let dp = CDate(d("Update Date").toString.Trim).Date
Where arrFilterDates.Contains(dp)
Select r = d).CopyToDataTable

Feel free to decompose the LINQ parts into essential activities

Find starter help here:
FilterLast7ConsecutiveDays.xaml (9.8 KB)

And for LINQ:
[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum

1 Like