Filter out rows by month

Help
1

Hi all

Is there a way utilizing the Filter DataTable activity or similar that I can filter out instances which have dates falling in July, August and October in column C

Thanks in advance!

image
image399×764 9.54 KB

1 Like
2

Hi @ChrisPals
Use assign activity where dt=dt.Select("[column name]='Datetime.now.tostring.Addmonths(0). ToString (“dd-mm-yyyy”) or (1) and (2)

Based on this you can write

Thanks
Ashwin.S

1 Like
3

Hi Ashwin

Thank for the answer.

I am not sure I understand completely though, could you elaborate or provide an example of how to use your method.

Thank you!

2 Likes
4

Hello @ChrisPals

  1. read excel using Read Range activity
  2. Use assign activity
    image
    image952×413 16.9 KB

make a variable with datarow type then perform assign activity because of Select method return datarow type

Addmonths(6) where 6 is month of july

hope you understand

Thanks

3 Likes
5

Thank you for the answer

This would create a datarow with july only right?

I need to remove instances in July, August and October, from the sheet.

2 Likes
6

@ChrisPals
Yes it’s create datarows only July
To remove instance you use same select method with Or operators and add condition like addmonth(7)
We can use not equal instead of = with or operators then you get datarow with other then July August row.
Then write that data in Excel again.

Thanks

1 Like
7

Hi @sandeep13

Thank you, I will definitely try this out.

2 Likes
8

Hi again

What if i do not have column names, can I use index or the column letter, like “I” or “J”

2 Likes
9

yes ,you can use index if you do not have column name

Thanks

1 Like
10

Hmm… it seems that I cannot use an int as the column name. Might need conversion.

image
image1084×586 36.7 KB

1 Like
11

send screenshot where you get record or assign activity screenshot

Thanks

1 Like
12

@ChrisPals
access column as dtvar.Columns.Item(1)

Thanks

1 Like
13

Hi @sandeep13

I am sorry but I am getting more confused as to how your last suggestion should be incorporated, could you provide how you would write the assign activity?

I am getting to something like this:
DTrow=DT.Select(“9<>'”+Datetime.Now.AddMonths(7).ToString() or Datetime.Now.AddMonths(8).ToString or Datetime.Now.AddMonths(10).ToString +“'”)

But i am getting 'Option strict on dissallows impllicit conversion from ‘String’ to ‘Long’. Could this be same issue as above.

1 Like
14

hello @ChrisPals

once print this value DT.Columns.Item(1).ColumnName

then try this

DTrow=DT.Select(DT.Columns.Item(1).ColumnName != Datetime.Now.AddMonths(7).ToString() or Datetime.Now.AddMonths(8).ToString or Datetime.Now.AddMonths(10).ToString +“’”)

1 Like
15

Hello @sandeep13

I tried it out, but unfortunately i get the ‘Identifier expected’ error.

image
image1212×616 27.8 KB

1 Like
16

Hi @ChrisPals

Use "dt.column.item(1).columnname!=condition

Double quotes are missing check it

Thanks
Ashwin.S

1 Like
17

Any Syntex error exist.please correct it.

2 Likes
18

Hi @sandeep13 and @AshwinS2

Thank you for the effort!

I solved in another way. Instead of filtering the dates out I decided to split the string and only run instances where the month where not July,August or October.

2 Likes
19

I would try to use some regex like this :

(\d)+[-.][09|10|11]±(\d)+