Nested filter in datatable

Build
, , , ,
#1

I have a bunch of excel files that looks similiarly to something like this:
Untitled
I have to filter the data by column 2 and 3 before writing it into a new excel, the condition of the filter is that if:

col.2 is 8 AND col.3 is =1,
OR col.2 is 9 AND col.3 >=2

then they belong to the same group and the rest of the data are filtered

The filter data table activity works well only if i have just a single excel, but i have multiple excel all with different number, how do i filter it so that the condition is still:

if col.2 = A and col.3=1 OR
col.2=A+1 and col.3>=2 ?

#2

Hi @Zac_Soh

Based on filter you can do the concept with if condition or Datatable select

if(row(“Column 2”).ToString=“A”) or row(“Col3”).ToString=“1” and row(“Col2”).ToString=“A+1” and row(“Col3”).ToString>=2

check it

Thanks
Ashwin S

#3

How do i do if condition for that? sorry i am new to this.
The column to can range up to 12 which means i have to repeat the code 12 times?

#4

@Zac_Soh

Use the if inside “for each row” activity.

Happy Automation:)

#5

@Blesslin
Thanks for the help
so i have to put if condition on “else” all the way for all my value?

#6

@Zac_Soh
Refer the xaml for the solution
Sequence1.xaml (8.6 KB)

so i have to put if condition on “else” all the way for all my value?

You can put in “if Else” also , for this case you need not put anything in “Else” part.

Happy Automation :smiley: :smiley:

#7

@Blesslin that only works if i have only 8/9 of the excel files, unfortunately i have multiple files to read that range from 1 to 12.

Is there a way to satisfy= both col.2=8/col.3=1 and col.2=9/Col.3>=2?
the loop gave me a problem where as if 1 of them exist then the execute the filter instead of check both condition

#8

did you try with the xaml ?

#9

yes i did,for your info 8/1 & 9/>2 = the month of sept. so basically i need to read files of different month as sometimes data from previous/next months are mixed into it.
The Sequence1 xaml you provided can only scan the month of sept but i need it to be able to scan all other months as well

#10

Use multiple “filter data table” activity, like for each month use one "filter data table activity "

#11

Hi @Zac_Soh

Look into the xaml file for your reference.
Nested_Fliter.xaml (10.5 KB)

Happy Automation :slight_smile:

#12

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.