I have a table that contains employee trainings and the date that they took them. The columns are “Training Name”, “Employee Name”, “Training Date” and “EmpID”.
The table is currently set up to list the training, the employee that took it, the date that employee took the training, and then the employee’s unique EmpID. Names/ID’s repeat from row to row if the employee took multiple trainings. Not all employees take all the trainings.
Build a new datatable dtfinal using Employee Name, Empd ID as columns
using for each item in the array training_names, use add column activity to add a column
Next use dtEmp = dt1.DefaultView.ToTable(True,{“Employee Name”,“EmpID”})
For each row in dtEmp, do the following
6a. Use assign activity to filter dt1 with respect to employee name and employee id
dtTemp= dt1.AsEnumerable().Where(Function(row1) row1(“EmpID”)= row(“EmpID”)).CopyToDataTable
a. create a list and initialize it
employee_data= New List(Of Object)
b. add row(“Employee Name”).ToString to employee_data using add to collection.
c. Repeat the step b process for EmpID as well
d. Loop through each training_names item and do the below operation d1. training_date= If(dtTemp.AsEnumerable().Where(Function(row2) row2(“Training Name”).ToString.Trim.Equals(level)).Count<>0,dtTemp.AsEnumerable().Where(Function(row2) row2(“Training Name”).ToString.Trim.Equals(level)),Rows(0)(“Training Date”).ToString,“”)
d2. Add training_date to employee_data list
once iteration for the training_data is completed, use add datarow to add employee_data.toArray() to dtfinal using add data row
At the end, write the dtfinal to excel using write range.
Use build datatable activity(outputdt) and add all the columns required in output table
Read the data from excel into Datatable(inputdt)
Use this in assign
outputdt = (From d in inputdt.AsEnumerable()
Group d By k=d("Employee Name").ToString,k1=d("EmpID").ToString into grp=Group
Let L1 = If(grp.Where(function(x) x("Traning Name").ToString.Equals("Level1")).Count>0,grp.Where(function(x) x("Traning Name").ToString.Equals("Level1")).Select(function(x) x("Training Date").ToString)(0),"")
Let ra = New Object(){k,K1,L1}
Select r = outputDT.Rows.Add(ra)).CopyToDataTable
Replicate this like multiple times for each level Let L1 = If(grp.Where(function(x) x("Traning Name").ToString.Equals("Level1")).Count>0,grp.Where(function(x) x("Traning Name").ToString.Equals("Level1")).Select(function(x) x("Training Date").ToString)(0),"")
and this add those names Let ra = New Object(){k,K1,L1,L2…}
In regards the shown statement, what does the ,Rows(0)(“Training Date”).ToString,“”) part of the statement do?
My understanding is the If statements consist of the if(Condition, Then, Else)
Condition is: dtTemp.AsEnumerable().Where(Function(row2) row2(“Training Name”).ToString.Trim.Equals(level)).Count<>0
if condition is true then: dtTemp.AsEnumerable().Where(Function(row2) row2(“Training Name”).ToString.Trim.Equals(level))
Else if false: Rows(0)(“Training Date”).ToString,“”)
This worked, thank you! Is it possible to format the (“Training Date”).ToString so it is in the mm/dd/yyyy format? It is currently displaying as mm/dd/yyyy hh:mm:ss