and expected result is by getting the no. of months from date calumn , I will get December,February & June . These I will add to dt1 table
Emp date December February June
Now I need to add relevant amount in their respective months by checking the emp ID & month
example : if empid=empid & month = December then add amount in December if exists else put NA
Expected result
Emp
date
December
February
June
1
1/12/2020
2000
NA
NA
2
2/2/2020
NA
900
NA
3
3/6/2020
NA
NA
NA
I tried with below linq query first to Add one month column data but its throwing error
(From d1 In dt1.AsEnumerable()
Group Join d2 In dt2.AsEnumerable()
On d1(“emp”).ToString().Trim() Equals d2(“emp”).ToString().Trim()
Into joinedRows = Group
From jrow In joinedRows.DefaultIfEmpty()
Let arrSet1 As Object() = {“emp”, “date”}.Select(Function(e1) d1(e1)).ToArray()
Let arrSet2 As Object() = {“Amount”}.Select(Function(ee) If(jrow IsNot Nothing AndAlso jrow(“Month”).ToString() = “December”, 0, “NA”)).Cast(Of Object).ToArray()
Let ra1 = arrSet1
Let raEmpty = arrSet2
Let ra2 = If(IsNothing(jrow), raEmpty, {“Amount”}.Select(Function(e2) If(jrow IsNot Nothing AndAlso jrow(“Month”).ToString() = “December”, jrow(e2), 0)).Cast(Of Object).ToArray())
Let ra = ra1.Concat(ra2).Cast(Of Object).ToArray()
Select r = result.Rows.Add(ra)).CopyToDataTable
result = New DataTable
result.Columns.Add("Emp", GetType(String))
result.Columns.Add("date", GetType(String)) ' Change the type to String
result.Columns.Add("December", GetType(String))
result.Columns.Add("February", GetType(String))
result.Columns.Add("June", GetType(String))
For Each rowDt1 As DataRow In dt1.Rows
Dim empId As String = rowDt1("Emp").ToString().Trim()
Dim decemberAmount As String = "NA"
Dim februaryAmount As String = "NA"
Dim juneAmount As String = "NA"
Dim decemberRow = dt2.AsEnumerable().FirstOrDefault(Function(d2) d2("Emp").ToString().Trim() = empId AndAlso d2("Month").ToString() = "December")
If decemberRow IsNot Nothing Then
decemberAmount = decemberRow("Amount").ToString()
End If
Dim februaryRow = dt2.AsEnumerable().FirstOrDefault(Function(d2) d2("Emp").ToString().Trim() = empId AndAlso d2("Month").ToString() = "February")
If februaryRow IsNot Nothing Then
februaryAmount = februaryRow("Amount").ToString()
End If
Dim juneRow = dt2.AsEnumerable().FirstOrDefault(Function(d2) d2("Emp").ToString().Trim() = empId AndAlso d2("Month").ToString() = "June")
If juneRow IsNot Nothing Then
juneAmount = juneRow("Amount").ToString()
End If
' Format the date to remove the timestamp
Dim dateValue As DateTime
If DateTime.TryParse(rowDt1("date").ToString(), dateValue) Then
Dim formattedDate As String = dateValue.ToString("dd/MM/yyyy")
result.Rows.Add(empId, formattedDate, decemberAmount, februaryAmount, juneAmount)
Else
' Handle the case where date parsing fails
result.Rows.Add(empId, rowDt1("date"), decemberAmount, februaryAmount, juneAmount)
End If
Next
Assign activity:
result = (From d1 In dt1.AsEnumerable()
Group Join d2 In dt2.AsEnumerable()
On d1(“emp”).ToString().Trim() Equals d2(“emp”).ToString().Trim()
Into joinedRows = Group
Let arrSet1 As Object() = {“emp”, “date”}.Select(Function(e1) d1(e1)).ToArray()
Let arrSet2 As Object() = {“December”, “February”, “June”}.Select(Function(ee) If(joinedRows.Any() AndAlso joinedRows.Any(Function(x) x(“Month”).ToString() = ee), joinedRows.First(Function(x) x(“Month”).ToString() = ee)(“Amount”), “NA”)).ToArray()
Let ra1 = arrSet1
Let ra2 = arrSet2
Let ra = ra1.Concat(ra2).Cast(Of Object).ToArray()
Select r = result.Rows.Add(ra)).CopyToDataTable
Log Message activity:
LogMessage(“DataTable result created successfully.”)
Thanks for the reply but here the months are dynamic… So Im creating a output table(resultdt) with two columns …
emp & Date - dt1 data will be added
Later I will get all the distinct months & using a for loop I will add columns for each month… so once I add column …
Is there any way through linq to just populate that column by checking emp ID & month in dt2 … similarly for rest months
example : I get unique months june,july & september
For each month in monthsList
{
Add column - month
add column data for month in resultdt
}
result_dt = New DataTable
result_dt.Columns.Add("Emp", GetType(String))
result_dt.Columns.Add("date", GetType(String))
' Extract distinct months dynamically from dt1 and order them
Dim distinctMonths As List(Of DateTime) = Dt1.AsEnumerable() _
.Select(Function(row) DateTime.ParseExact(row("date").ToString().Trim(), "d/M/yyyy", CultureInfo.InvariantCulture)) _
.Distinct() _
.OrderBy(Function(dateValue) dateValue) _
.ToList()
Dim formattedDates As List(Of String) = distinctMonths.Select(Function(dateValue) dateValue.ToString("MMMM", CultureInfo.InvariantCulture)).ToList()
For Each month As String In formattedDates
result_dt.Columns.Add(month, GetType(String))
Next
For Each rowDt1 As DataRow In Dt1.Rows
Dim empId As String = rowDt1("Emp").ToString().Trim()
Dim rowValues As List(Of Object) = New List(Of Object)
rowValues.Add(empId)
Dim dateStr As String = rowDt1("date").ToString()
Dim formattedDate As String
Try
Dim dateValue As DateTime = DateTime.ParseExact(dateStr, "d/M/yyyy", CultureInfo.InvariantCulture)
formattedDate = dateValue.ToString("MMMM", CultureInfo.InvariantCulture)
rowValues.Add(dateValue.ToString("dd/MM/yyyy"))
Catch ex As Exception
' Handle the case where date parsing fails
formattedDate = dateStr
rowValues.Add(dateStr)
End Try
For Each month As String In formattedDates
rowValues.Add("NA")
Next
For Each rowDt2 As DataRow In Dt2.AsEnumerable().Where(Function(d2) d2("Emp").ToString().Trim() = empId)
Dim month As String = rowDt2("Month").ToString().Trim()
Dim amount As String = rowDt2("Amount").ToString()
Dim index As Integer = formattedDates.IndexOf(month)
If index <> -1 Then
rowValues(index + 2) = amount
End If
Next
result_dt.Rows.Add(rowValues.ToArray())
Next
resultdt = … (initialize or get your result DataTable)
dt2 = … (initialize or get your dt2 DataTable)
monthsList = {‘June’, ‘July’, ‘September’} ’ Replace this with your actual list of unique months
For Each month In monthsList
Add a column for the current month in resultdt
resultdt.Columns.Add(month, GetType(String))
Populate the column with data from dt2 based on emp ID and month
For Each row In resultdt.Rows
empID = row("emp").ToString()
filterExpression = $"emp = '{empID}' AND Month = '{month}'"
matchingRows = dt2.Select(filterExpression)
Assuming there is only one matching row, adjust logic if needed
If matchingRows.Length > 0 Then
row(month) = matchingRows(0)("Amount").ToString() ' Replace "Amount" with the actual column name from dt2
Else
row(month) = "NA" ' Or any default value if no match is found
End
You don’t need LINQ for this, nor any complicated code. Just use the Join Data Table activity. It’s what it’s for. What you’re trying to do is a standard database operation called a join.
I dont think its a simple table join . Because we are adding dynamic columns based on first table data and filling those columns from a different column in first table data.
Anil_G
ashokkarale
vrdabberu
Pujitha_Ande
Ajay_Mishra
Yoichi
anjani_priya
postwick
Marisa_Ontiveros1
J0ska