Filter records based on dynamic column values

[avinashy (Avinash)]

Hi ,i have a tble like below .i want to filter to filter the records based on column “department” which is dynamic,where the values under that column may increase.

After filtering based on columns want to write in different excels.

can anyone help me on this.

Thanks

[Palaniyappan (Palaniyappan P)]

we can use FILTER DATATABLE activity where we can mention a VARIABLE in the value field
so that the filter will be done based on the value passed to that variable which might change dynamically along the process

were you facing any issue in using that
Cheers @avinashy

[avinashy (Avinash)]

@Palaniyappan : my column values under department are dynamic.in that case how can i use this?

[Palaniyappan (Palaniyappan P)]

yah you can mention the variable name in the filter wizard under VALUE field…
that variable can hold dynamic value and can be assigned with dynamic value along the process
so that the filter datatable activity will filter that datatable based on the value in that variable
like this @avinashy

Cheers @avinashy

[lakshman (Ganta lakshman)]

@avinashy

Try these steps.

1. use below expression to find unique departments in the DataTable.

    uniqueDT = yourDT.DefaultView.Totable(True,"Department").CopyToDataTable
   

Then use ForEach Row activity to iterate one by one department.

          ForEach row in uniqueDT
               filterDT = yourDT.Select("[Department] = '"+row("Department").Tostring+"'").CopyToDataTable

And then write into Excel file.

[ranjith_kumar3 (ranjith kumar)]

how to filter values in a colum those values are dynamically change
for example in below image it contains 15000…719 in 10 times(it may be increse or decrese next time and those 15000…719 also chage) i want filter 15000…719 and next number 1500…717 in 3 times and 1500…715 and 15000…713 those numbers are increase and decrese.how to filter please help me out

[Vincent_Nuestro]

@lakshman

uniqueDT = yourDT.DefaultView.Totable(True,"Department").CopyToDataTable

hi @lakshman may i ask what variable type you used for this code

Im getting error

uniqueDT = yourDT.DefaultView.Totable(True,“Department”).CopyToDataTable

[vignesh.ks]

Hi @Vincent_Nuestro

Variable type as ‘DataTable’

Thanks, Happy Automation.

[Vincent_Nuestro]

hi @vignesh.ks

i changed it already. but getting same error
“CopyToDataTable is not a member of System.data.datable”

[lakshman (Ganta lakshman)]

@Vincent_Nuestro

Remove CopyToDataTable from that expression and then check it once.

[Vincent_Nuestro]

@lakshman

Getting different error inside for each

dtBotReportStakeholders.Select(“[Immediate Manager - Current] = '”+(“Immediate Manager - Current”).ToString +" ’ ").CopyToDataTable

Variable type: Datatable

[lakshman (Ganta lakshman)]

@Vincent_Nuestro

It should be like below.

            dtBotReportStakeholders.Select("[Immediate Manager - Current] = '"+roaster(“Immediate Manager - Current”).ToString+" ’ ").CopyToDataTable

[Vincent_Nuestro]

@lakshman

[Vincent_Nuestro]

hi @lakshman why it throw error like this?

[lakshman (Ganta lakshman)]

@Vincent_Nuestro

Can you please show me that expression once.

[Vincent_Nuestro]

hi @lakshman

dtBotReportStakeholders.DefaultView.ToTable(True, “Immediate Manager - Current”)

dtBotReportStakeholders.Select(“[Immediate Manager - Current] = '”+roaster(“Immediate Manager - Current”).ToString+" ’ ").CopyToDataTable

it is inside for each loop

[lakshman (Ganta lakshman)]

@Vincent_Nuestro

I guess you should pass dtMngrName DataTable to For Each Row activity instead of dtBotReportStakeHolders. Pass dtMngrName to it and then check once.

[Vincent_Nuestro]

@lakshman

[zubi.dewangan]

I want to apply nested for each, for each department(Outer loop), inside that for each Id.

My first for each loop for department is working fine, but when I am running the second loop it is itering all the ID’s, it should only loop through the ID which is having common department. Please assist me with this issue.

Thanks