Using if condition need to filter out and extract that filter data into another sheet in excel

[ashwini_avula]

Hello,
can anyone help me to create a workflow.
condition should be like this–>if this condition is true (Amount Spend >= Amount Allotted) then i need to extract that true data from excel sheet1 to sheet2 then need to send mail to one person with attached file.
Thanks in Advance…

[NIVED_NAMBIAR (NIVED N)]

Hi @ashwini_avula
welcome to forum

you can try this using linq query too

steps to be followed.

a. Read the excel and store in dt1 variable using read range.

b. build a datatable with required columns using build datatable activity and store in dt2

c . now use assign activity with below linq query

dt2 = (From row in dt1.AsEnumerable
Where CInt(row("Amount_Spend))>CInt(row(“Amount_Alloted”))
Let r1 = row.ItemArray
dt2.Rows.Add(r1)).CopyToDataTable

Try this way and let me know if it works

Regards,
Nived N
Happy Automation

[ashwini_avula]

Hello Nived,
I appreciate for your quick reply but I am getting error in assign activity. Can you check and update once.
Thanks…

[Pravin_Patil1 (Pravin Patil)]

@ashwini_avula

Try below expression

varDT.AsEnumerable.Where(function(r) cdbl(r("Amount_Alloted").ToString)>=cdbl(r("Amount_Spend").ToString)).CopyToDataTable

[Pravin_Patil1 (Pravin Patil)]

@ashwini_avula

Find attached below for reference

Excel.xaml (7.1 KB)

[copy_writes (chethan)]

what is that cdbl would you please explain that code

[Pravin_Patil1 (Pravin Patil)]

It is converting string to double

[Swetha_V]

Hello Pravin,
Can you explain this expression varDT.AsEnumerable.Where(function(r) once.
Thanks in advance…

[Pravin_Patil1 (Pravin Patil)]

@Swetha_V AsEnumerable method gives you the rows collection and we are filtering it with conditions…It is the best way to avoid for each row.

In other ways, UiPath made a document also.

Refer to the below link for more information.

Four Ways to Filter Data Tables in UiPath Studio | UiPath

[Swetha_V]

Hi Pravin,
Error showing in Assign activity. Could you help with this once?

[Pravin_Patil1 (Pravin Patil)]

Add new System.Data.Datatable in both variables default value

[Swetha_V]

Could you check this error once

[NIVED_NAMBIAR (NIVED N)]

Hi check with this query

Using if condition need to filter out and extract that filter data into another sheet in excel

Hi @ashwini_avula welcome to forum you can try this using linq query too steps to be followed. a. Read the excel and store in dt1 variable using read range. b. build a datatable with required columns using build datatable activity and store in dt2 c . now use assign activity with below linq query dt2 = (From row in dt1.AsEnumerable Where CInt(row("Amount_Spend))>CInt(row(“Amount_Alloted”)) Let r1 = row.ItemArray dt2.Rows.Add(r1)).CopyToDataTable Try this way and let me know if it work…

[Swetha_V]

Hello Pravin,
I am unable to filter the Allowed data to a different sheet. Can you help me with that?

Thanks in Advance…

[Pravin_Patil1 (Pravin Patil)]

@Swetha_V With filter data table activity are you getting any error or just an empty data table in return?

The select query for your solution would be like below.

Dt.Select("Status='Allowed'").CopyToDataTable

[Swetha_V]

Hello,
Can anyone help me with this error?

Thanks in advance…