Hi All,
i want to write linq query to filter below item for column name (
)
Approval and coding
Comment request
Receiving Resolution
170 verification procees for PO Invoice
And again one more filter in different column same file column Name - (Responsible User) i need to exclude below names if present
Agata Panek(APKNEK)
Alex Lee(ALLE7)
Alexander Fenn(AFENN)
Ali Hayder Tokay(ATOKAY)
Alia Salameh(ASALAME1)
how to achieve it.
Thanks in advance
Can you show a sample input and required output please…that way it would be easy to understand what you need
cheers
Hi @Anil_G ,
i have excel file with real time customer data, where in 2 columns i need to apply filter, after filtering few data will be automatically removed , then need to apply next activity.
Hope this will help you to understand my requirement
Hi @RAKESH_KUMAR_Tiwari ,
Maybe we could make use of Contains and Not Contains method within the Linq to filter the rows. As you have the list of values to be filtered, let us consider the below :
list1 = {"Approval and coding","Comment request","Receiving Resolution","170 verification procees for PO Invoice"}
list2 = {"Agata Panek(APKNEK)","Alex Lee(ALLE7)","Alexander Fenn(AFENN)","Ali Hayder","Tokay(ATOKAY)","Alia Salameh(ASALAME1)"}
Here, list1 and list2 are variables of type Array of String.
Next for the Linq Expression we can use the below :
FilteredDT = DT.AsEnumerable.Where(Function(x)list1.Contains(x("Column1").ToString) andAlso Not(list2.Contains(x("Column2").ToString)).CopyToDatatable
Here, FilteredDT is the Datatable variable which contains the Filtered values and DT is the Input datatable.
Let us know if the above method is understandable and if you were able to achieve the required output.
ok you have created list1 and list2 variable, in my case it is available in columns
below query i can write once i have filtered the data from column
FilteredDT = DT.AsEnumerable.Where(Function(x)list1.Contains(x(“Column1”).ToString) andAlso Not(list2.Contains(x(“Column2”).ToString)).CopyToDatatable.
my question is how to implement in my case
A Slight confusion again, If the values are in columns and you want to pick all the values in the columns, then the Filter for Column1 is not required at all, as you will be including all the column values.
The understanding here is that you would want to select only a few values from all the values present in the column and you would know these values before hand. Is this not the case ?
As suggested already, It would be better if you could provide us with a Sample Input and the Expected Output for that Input to be sure of the logic that we would need to apply.
List1 and list2 are the values you need to filter …whatever filters you need @supermanPunch has given all of them together in list and used them to filter your datatable
cheers
let say, i have below data.
i want to exclude some values from Responsible column if that names exists, again i want to select few Values like Comment Request etc… in Queue Column
i think this is very straight forward, i am one who is creating confusion here sorry for that.
i can use try filter datatable activity two time one for one column and another filter for another column, but sometimes this doesn’t work and also it will be slow, so i want to use query.
let me know if need more clarity on this.
Also, pls let me know how to replace list1 and list2 with real data which i have .
Please check this.To make it clear I have just re written the same formula
FilteredDT = DT.AsEnumerable.Where(Function(x) {"value1","value2"}.Any(x("Column1").ToString) andAlso Not({"valux","valuey"}.Any(x("Column2").ToString)).CopyToDatatable
Here value1 value2 valuex value y are the values that you want to filter
Hope this is clear
cheers
The some/few values mentioned are the values that you should be knowing beforehand if I’m not wrong.
So the creation of the list1 & list2 values were based on those some values that you have mentioned in the initial post.
ok, let me try then update you,
but before that i got one small issue, actually bot writing the extarcted data in excel but everything as text, but i want it should convert to Number in (Amount) column only for everytime when bot runs again and extratct the data.
how to achieve it
In your extract datatable wizard choose …column type as number instead of text
Cheers
Table settings would have it…easier way is to use extract datatable again and in the wizard you can select from dropdown
cheers
Maybe for this, If you do already know the Column order in the table, we should be able to create a Excel Template having the columns names in the required format, meaning we can set the Column to General, Number, Date, etc… Then we can use Write Range activity with Add Headers unchecked with range starting from A2. After the data is entered, it should correspond to the required format in the Excel.
One other way is to use Format Cells (Modern) activity, to format the specific column to the Required type.
i am trying format cell activity , it is just deleting all the data,
img for ref
i have given range G2:G500, so everytime G2 will not have 500 range, but for saftey purpose i have provided, since it doesn’t have 500 range so throwing error, how to handle dynamically
Maybe you have specified wrongly. It must be G2:G500, Try this and let us know, Also maybe try with G:G
haha, it is deleting all the data, very starnge, i don’t know how to rely on this activity or not
That shouldn’t be the case, Is it possible for you to provide the Screenshot of it’s implementation ? Or maybe the workflow itself ?
