Hi All,
I m trying to use Linq for the below steps:
There are 2 data tables Dt1 is having [Year,Eventcode,Col1,Col2,Col3,Col4] and Dt2 is having [Year,Eventcode,Col5,Col6].
There will be several event codes, the operation I m trying to perform is to search for the"Years" which has the same event codes from both the datable, and upend them in another datatable Outputdt with columns [Year,EventCode,Col1,Col2,Col3,COl4,Col5,Col6] . The “Year” will differ in both the datatable for eg, Dt1 will have the entries for 2011 but Dt2 may not have any entries for that year. For these types of cases, I want the respective columns to be updated as “0” for eg. here OutputDt=[2011,abc,1,2,3,4,0,0].
I tired the below linq but I m getting a repeated entries instead of the expected ones.
(
From a In dt_CalculationLogic1.AsEnumerable
From b In dt_CalculationLogic2.AsEnumerable
Let x=a(“Year”).ToString
Let y=b(“Year”).ToString
Let d=a(“Col1”).ToString
Let e=a(“Col2”).ToString
Let f=a(“Col3”).ToString
Let g=a(“Col4”).ToString
Let h=b(“Col5”).ToString
Let i=b(“Col6”).ToString
Let j=0
Let n=j.ToString
Select dt_MasterFile.Rows.Add((If((x=y),x,(If((CInt (x)<CInt (y)And x IsNot Nothing),x,y)))),a(“EventCode”).ToString,(If((x=y),d,(If((CInt(x)<CInt(y)And x IsNot Nothing),d,n)))),(If((x=y),e,(If((CInt(x)<CInt(y)And x IsNot Nothing),e,n)))),(If((x=y),f,(If((CInt(x)<CInt(y)And x IsNot Nothing),f,n)))),(If((x=y),g,(If((CInt(x)<CInt(y)And x IsNot Nothing),g,n)))),(If((x=y),h,(If((CInt(x)<CInt(y)And x IsNot Nothing),n,h)))),(If((x=y),i,(If((CInt(x)<CInt(y)And x IsNot Nothing),n,i)))))
).CopyToDataTable
Sure @Shiva_Nikhil
I want to update the output datatable which is dt_MasterFile with the following columns [year, Eventcode, column1, column2, column3, column4, column5, column6] for the eventcode which will be retrieved from the user input(using input dialog activity) for a particular year. I have two input datatables here it is dt_CalculationLogic1 having [Year, EventCode, Column1, Column2, Column3, COlumn4] and dt_CalculationLogic2 haveing [Year, EventCode, COlumn5, COlumn6]. for eg: if dt_calculationlogic1 the entries will be [2011,abc,1,2,3,4] and the dt_caculation/logic2 will have [2011,abc,5,6] so the output should be dt_MasterFile [2011, abc,1,2,3,4,5,6]. there are cases where first data table may have a year which is not in second data table for which i want the column values which doesnt have that year as “0”.
Dim matchedIndex As Int32
dt1.Columns.add("Column5")
dt1.Columns.add("Column6")
For Each r As datarow In dt1.AsEnumerable
If dt2.AsEnumerable.Any(Function(a) a("Year").ToString.Trim.Equals(r("Year").ToString)) Then
matchedIndex=dt2.AsEnumerable.ToList.FindIndex(Function(b) b("Year").tostring.equals(r("Year").ToString.Trim))
r("Column5")=dt2.Rows(matchedIndex).Item("Column5")
r("Column6")=dt2.rows(matchedIndex).Item("Column6")
Else
r("Column5")="0"
r("Column6")="0"
End If
Next
Thank you very much for the above.
Actually I m trying to combine both the datatables. There is a case in which the dt2 may have 2017 data but the dt1 wont have that entries, so in that case i want only the column 5 and column 6 data for that particular year in the dt1 mentioning all other column as 0, dt1 [2017, abc, 0,0,0,0,5,6].
I m totally new to the linq so i m quite lost. If possible can you please explain me the Dim function and the code how it works.
ashokkarale
sharazkm32
sonaliaggarwal47
LamaX