Filter the column based on completed

Help Studio
, ,
21

Hi
@Parvathy I’m Sorry, I was a bit confused.
We have to filter the date also same as we are filtering the state.
For the dates I need to check
If the code running is between Tuesday-Saturday than we have to filter the date before one day from the date already present in the column.
Example:
Today is 01-08-2023 and day is Tuesday.
Than I need to filter 1 day from the date mentioned
so date mentioned is
18-07-2023
and filtering it as it it tuesday
so the day that we will be saving is the datatable is 17-07-2023.
Thanks!!

22

Hi @Kunal_Jain
I will give you updated workflow in a short while.

Regards,

23

For Sure
Thank You Very Much!!

24

Hi @Kunal_Jain

Check out the below zip file. Hope it meets your output requirment.

4.zip (168.5 KB)

Hope it works!!

25

Hi @Parvathy
Write Range Workbook: Unable to set cell value to 11/30/1899 00:00:00
I am getting this error while running the process.
How should we resolve it?

26

@Kunal_Jain
Send only the date value to excel.

Regards,

27

I am sending just the date as you mentioned in the provided code.
I have not made any change in the code.

28

@Parvathy
Is there any change I need to make regarding this issue?

29

Send me the workflow. I will let you know @Kunal_Jain

Regards

31

Hi @Parvathy
I cannot share the workflow. I am sorry for that
But I am sharing the Original excel sheet which is giving me the error.
ViewAllEqosExport.xlsx (1.3 MB)
In this excel
We need to Change the following as per testing excel that I provided:
State will be changed with :-“Status”
And Date will be changed with :- “Completion Date”
Thanks in advance!!

32

Hi @Parvathy
Can you help me with the solution for the same please?
Thanks in advance!!

33

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

34

@Kunal_Jain

Try this

Read the data into datatable then

filtereddt = Dt.AsEnumerable.Where(function(x) x("State").ToString.Equals("QLD") AndAlso If(Now.DayOfWeek.ToString.Equals("Monday"),Cdate(x("Date").ToString)<Now.Adddays(-1) AndAlso Cdate(x("Date").ToString)>Now.Adddays(-4), Cdate(x("Date").ToString).ToString("MMddyyyy").Equals(Now.Adddays(-1).ToString("MMddyyyy")))).CopyToDataTable

Also it is better to check .Count>0 before copying to check fi there are any rows

Cheers

35

Hi @Anil_G
Please check the above links
1st :- Filter the column based on completed - #24 by Parvathy
2nd:- Filter the column based on completed - #25 by Kunal_Jain
3rd:- Filter the column based on completed - #31 by Kunal_Jain
I have tried solving but was getting error Write Range Workbook: Unable to set cell value to 11/30/1899 00:00:00
I am getting this error while running the process.
Can you please suggest something regarding the changes in the above code please?
Thanks in advance!!

36

@Kunal_Jain

For further analysis can you show the datatable before writing to excel how it looks from
The locals panel…and where this particular data is coming

One thing we can do is to add a ‘ at the start of the values to make them as string and then write

Cheers

37

Hi @Anil_G
I have checked the data but I am not getting the error.
Can you please suggest how we can make it as string and write it in excel sheet?
Thanks in advance.

38

@Kunal_Jain

use this in invoke code. Send dt as in/out and also column name should be the column which contains date

dt.AsEnumerable.ToList.ForEach(Sub(x) x("ColumnName") = "'" + x("ColumnName").ToString)

cheers

39

Hi @Anil_G

image
image708×188 8.09 KB

I am getting this error.

40

@Kunal_Jain

Instead of function use Sub

That should solve…check edited formula above

cheers

41

Hi @Anil_G
No it is not solving the issue
Write Range Workbook: Unable to set cell value to 11/30/1899 00:00:00.
If you say I can attach the workflow.