Filter the column based on completed

Help Studio
, ,
1

Hi,
I have an excel sheet that contains some columns.
We need to Check for “QLD” in state column.
And based on it I need to check the corresponding date and
⦁ If the current day is Tuesday – Friday, filter on current date – 1.
⦁ If the current day is Monday, filter on dates for the last Friday – Sunday
I have attached the excel sheet for the same.
Testing_Excel.xlsx (11.2 KB)
I have also attached the screen shot of what the requirement is.

image
image1366×768 110 KB

image
image1366×768 90.2 KB

I just need to check the dates for the selected check box in sort filter and write it into a new sheet.
Can anyone help me please?
Thanks in advance!!

2

Hi @Kunal_Jain

There is no column based on status= Completed. Can you provide your requirements so that i can provide you logic.

Regards,

3

We need to Check for “QLD” in state column.

4

@supermanPunch @Gokul001 @ushu @kirankumar.mahanthi1 @Palaniyappan @Sudharsan_Ka @Anil_G
Can anyone help me with the approach?

5

Ok . Just make clear with your date requirment @Kunal_Jain

Regards,

6

Take an example of todays date and day
Today is Monday so according to the requirement I will have to filter on dates for last Friday to Sunday.
Subtracting the required amount of days

7

Hi @Kunal_Jain

  1. Use Read Range Workbook to read the excel and store it an DataTable say dt_data.
  2. Use Filter Data Table activity and in configure filter given the following condition:
Input:dt_data
Configure Filter: "State"="QLD"
Output:filteredDataTable
  1. Use For Each Row in Data Table activity to iterate throughfilteredDataTable.
  2. Use assign activty and give the below condition:
currentDate=CDate(CurrentRow("Date").ToString)

DataType of currentDate: System.DateTime
4. Use Else If activity and give the condition:

currentDate.DayOfWeek>=DayOfWeek.Tuesday AndAlso currentDate.DayOfWeek<=DayOfWeek.Friday
Then
        currentDate= currentDate.AddDays(-1)
        Use Message Box: currentDate.ToString("MM/dd/yyyy")
Else If
     currentDate.DayOfWeek=DayOfWeek.Monday
Then
      currentDate= currentDate.AddDays(-3)
      Use Message Box: currentDate.ToString("MM/dd/yyyy")

Check out the below workflow file. Hope it meets your requirement.

Sequence17.xaml (13.7 KB)

Hope it works!!
Regards,

8

Hi @Kunal_Jain - You can use below expression to meet your requirement. Take assign activity

DT = DT.Select("[State] = 'QLD'"+"AND [Date] >= #"+PeriodInit.ToString +"# AND [Date] <= #"+PeriodEnd.ToString+"#").CopyToDataTable

  • DT is the data table variable that has the excel data

  • The above exp filters the data from State, and also get the date values starting from 3 days back from the current day to yesterday

  • PeriodInit, PeriodEnd are the variables that has starting and ending date

PeriodInit(String) = DateTime.Now.AddDays(-3).ToString("MM-dd-yyyy")

PeriodEnd(String) = DateTime.Now.AddDays(-1).ToString("MM-dd-yyyy")

Please check the attached workflow

SampleWorkflow.zip (11.7 KB)

9

This document is showing invalid it is not opening in UiPath studio

10

I am getting Errors.
Assign: The source contains no DataRows.

11

@Kunal_Jain - Did you get a chance to check the workflow that I attached above. Can you please check your data table has a data by printing the rows count

12

Hi @ushu
I have checked it
It is giving the error at the assign activity
DT.Select(“[State] = ‘QLD’”+“AND [Date] >= #”+PeriodInit.ToString +“# AND [Date] <= #”+PeriodEnd.ToString+“#”).CopyToDataTable.

13

@ushu And also it must pass all the values present in the excel based on the filter.
I have attached screenshot of what is required in the above thread.

image
image1366×768 90.2 KB

This has to be the final output
Thanks!!

14

@Kunal_Jain - Can you share your sample input excel

15

Testing_Excel.xlsx (11.2 KB)

16

@Kunal_Jain - As per your above screenshot, you need the values if the State column is equals to QLD. is that right??

17

Yes and also need to check the date if state is QLD
And based on it I need to check the corresponding date and
⦁ If the current day is Tuesday – Friday, filter on current date – 1.
⦁ If the current day is Monday, filter on dates for the last Friday – Sunday

18

@Kunal_Jain - The above attached workflow should work

  • The workflow looks for the State column with QLD and also it check the respective dates requirement that you mentioned
  • But, in the excel that you have the given dates were not matching. Hence, it shows a exception saying Source contain no data rows
  • Look at the below screenshot, since the state matches with QLD but the there is no date that matches

out
out903×249 123 KB

  • if it is Monday, you need to filter the dates from friday - sunday
  • Tuesday - dates should match from Sat - Monday. But, I don’t see the matched dates hence the output is missing

Please change the dates in your excel and give a try. If I’m missing something let me know

19

For the dates I need to check
If the code running is between Tuesday-Saturday than we have to subtract one day from the date already present in the column.
Example:
Today is 01-08-2023 and day is Tuesday.
Than I need to subtract 1 day from the date mentioned
so date mentioned is
18-07-2023
and subtracting it as it it tuesday
so the day that we will be saving is the datatable is 17-07-2023.
Thanks!!

20

Hi @Kunal_Jain

Check out the below Zip file.

4.zip (160.0 KB)

Hope it works
Regards,