Apply advanced filter in datatable

datatable
excel
activities

#1

Hi all folks,

i need to apply an advandced filter, mixing the values from two columns.
i will try explain this further …
i have an excel with three columns, A,B and C

the values from the column A can be repeated, the values from the column B can be repeated, and the values from the column C also can be repeated.

i need to filter the sheet using in first place the column B, the result would be like this:

the next filter is more complex …i have to filter using specifics values from the columb C , i.e. using the values 3 and 5 and the result would be like this:

image

so i need help with this folks. i have no idea how to apply two or more filters.

could you help me please?

thanks in advance!!


#2

Hi @cacafundi,

DataView view = new DataView(table);
DataTable distinctValues = view.ToTable(true, "COLUMN A", "COLUMN B")

Regards,
Arivu


#3

Hi Arivu96,

thanks for the reply, the only thing is that i need to get concrete numbers from column C, for example showing the numbers that i want, i.e. 3 and 5. I can pass the values that i want

please could you help me to reach this.?


#4

@cacafundi

Use this query

  1. Create one list of values on which you want to filter third column. let us assume list a

(From p in dt1.AsEnumerable()
where Convert.Tostring(p.Item(“ColumnB”)).Equals((From q in dt1.AsEnumerable() Select Convert.ToString(q.Item(“ColumnB”)).ToList(0)) and lista.contains(Convert.Tostring(p.Item(“ColumnB”)))
select p).ToArray.CopyToDataTable

Regards
Mahesh


#5

@cacafundi, Use this Query,

  1. Read the excel with add headers property checked and save to datatable dt

  2. In Assign activity, save this query to a datatable variable
    (From row in dt.Select() Where row("ColumnB").ToString.Equals("20180101") and (row("ColumnC").ToString.Equals("3") or row("ColumnC").ToString.Equals("5")) Select row).CopyToDatatable

Regards,
Dominic :slight_smile:


#6

Hi @cacafundi,

If you want to filter column b is 20180101 and column c is 3 use below code to get the data
Dt.Select("[COLUMN B]='20180101' and [COLUMN C]='3'").CopyToDataTable()

If you want to filter column b is 20180101 and column c is 5 use below code to get the data
Dt.Select("[COLUMN B]='20180101' and [COLUMN C]='5'").CopyToDataTable()

Regards,
Arivu


#7

Thank you all :man_student:

you are amazing !!!

i will try those solutions , thank you very much :clap::clap::clap:

BR.


#8

@arivu96 bro, We can also use like this,
@cacafundi @MAHESH1

Dt.Select("[COLUMN B]='20180101' and [COLUMN C] IN ('3','5')").CopyToDataTable()

Regards,
Dominic :slight_smile:


#9

@Dominic @cacafundi @arivu96

we have to filter the second column based on the first value , So I took the first element by taking the second column as a list. suppose in the first place instead of ‘20180101’ if some other value is there then we have to take that value.

I think this is the requirement. Sorry if I am wrong.

Regards,
Mahesh


#10

Hi all,

the solution from Dominic Works for me… thanks!!!:wink:

BR.


#11

@arivu96 Dt.Select("[COLUMN B]=‘20180101’").CopyToDataTable() in the above statement can i give array variable which contains different values instead of “20180101”