(
From R1 In DataTbl.AsEnumerable
Let Check = lookup_values.AsEnumerable.Where(Function(R2) R2(0).ToString.Trim.Equals(R1(1).ToString.Trim)).ToList
Let Check_Flag = If(Check.Count() > 0, Check(0), Check(1))
Let Flag = {R1(1), Check_Flag}
Select OP_DT.Rows.Add(Flag)
).CopyToDataTable
If your Check.Count > 0 then you have to use below code, Let Check_Flag = If(Check.Count() > 0, Check(0), "List Count is Zero")
And you can’t use ‘Check(1)’ in Else part of If condition, if your Check.Count is zero.
@Manish540 Its still giving error,
We need data for all the target columns in build data columns and their sequence is not same as that in source data table.
I have fixed the issue, please use this below code in your assign and let me know,
(
From R1 In DataTbl.AsEnumerable
Let Check = lookup_values.AsEnumerable.Where(Function(R2) R2(0).ToString.Trim.Equals(R1(1).ToString.Trim)).ToList()
Let Check_Flag = If(Check.Count() > 0, Check(0).ToString, String.Empty)
Let Flag = {R1(1).ToString,Check_Flag}
Select OP_DT.Rows.Add(Flag)
).CopyToDataTable
If you want to get “Category” column value from DataTbl table to OP_DT table for matching ‘Reference Number’ in lookup_values then use this below query,
(
From R1 In DataTbl.AsEnumerable
Let Check = lookup_values.AsEnumerable.Where(Function(R2) R2(0).ToString.Trim.Equals(R1(1).ToString.Trim)).ToList()
Let Check_Flag = If(Check.Count() > 0, R1("Category").ToString, String.Empty)
Let Flag = {R1(1).ToString,Check_Flag}
Select OP_DT.Rows.Add(Flag)
).CopyToDataTable
Hi @Manish540
This is good and helps, but is there any approach/way to optimize the query so that it gives output only for required “Reference numbers” as mentioned “lookup_values” table, also it should give output for all target columns mentioned in “OP_DT” table in 1 go.
Do you require a Linq approach to perform this strictly, it does seem that you would want to perform a Join Operation (Inner Join), we can do this using the Join Datatables Activity.
If already tried with Join Datatables Activity, let us know what was not working with it.
Then from the resultant Datatable we can keep only the required columns.
OP_DT = (From R1 In DataTbl.AsEnumerable
Join R2 In lookup_values.AsEnumerable
On R1("Reference Number").toString.Trim Equals R2("Reference Number").ToString.Trim
Let ra = R1.ItemArray
Select OP_DT.Rows.Add(ra)).CopyToDataTable
For this to be properly executed, you would need to modify a previous step, where we wouldn’t need to use Build Datatable Activity to prepare the columns, rather you could create an array of String which contains the column names that you would want to preserve in the output datatable. We can name the array of string variables as requiredColumns, so you would need to do the below using an Assign Activity.
So Instead of the Build Datatable activity, you could use the below expression in the Assign Activity. Make sure to do the below after the DataTbl is read using Read Range activity.
OP_DT = DataTbl.Clone
At the last, after the Linq Expression is used, you can use the below Expression to preserve only the required columns in the Output Datatable.
Maybe this is a re-work that you would need to do, but this is also an alternate way.
Also, as I have taken a look at the data that you have, it seems in the Look up values sheet, there is only one column, this would also mean that we could straight up perform a Filtration on the DataTable sheet and get the required output.
ashokkarale
Yoichi
singh_sumit
sven.wullum1
adi.mehare
ppr
sonaliaggarwal47
shahidh.aqeel.shahul
Akash_Javalekar1
Sami_Rajput