How do I reformat rows to columns?

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.

Example of current data format:

I would like to reformat it so there is only 1 EmpID/Employee Name per row with all of their training dates under columns with Training Names.

Example:

Any feedback on the best way to go about this would be greatly appreciated.

Thank you.

Hi @mbuehler

Try this way

  1. Read the excel file and store in datatable dt1

  2. using assign activity, assign an array as below

training_names= dt1.AsEnumerable().Select(Function(row) row(“Training Name”).ToString.Trim).ToList().Distinct().ToArray()

  1. Build a new datatable dtfinal using Employee Name, Empd ID as columns

  2. using for each item in the array training_names, use add column activity to add a column

  3. Next use dtEmp = dt1.DefaultView.ToTable(True,{“Employee Name”,“EmpID”})

  4. 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.

This is complete step to acheieve it.

Let me know if you have any questions :slight_smile:

Thanks & Regards,
Nived N

1 Like

@mbuehler

Please follow the steps

  1. Use build datatable activity(outputdt) and add all the columns required in output table
  2. Read the data from excel into Datatable(inputdt)
  3. 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…}

Hope this helps

cheers

1 Like

Is there a step missing under 6? It is skipping from c to e and it seems like there would be a Assign in there that I am missing.

Sorry

Steps was correct but used e instead of d

Sorry for that, I have edited my response

1 Like

I guess I am ultimately not understanding how to set up the loop through each training_data item then. what is the training_data populated with?

it is training_names which has the names of training names. I have edited my response.

Kindly check that :slight_smile:

1 Like

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,“”)

Check this

Condition: dtTemp.AsEnumerable().Where(Function(row2) row2(“Training Name”).ToString.Trim.Equals(level)).Count<>0

Then: dtTemp.AsEnumerable().Where(Function(row2) row2(“Training Name”).ToString.Trim.Equals(level))(0)(“Training Date”).ToString

Else: “”

This condition is added so that it would not throw any error if the employee does not have record of level which other employee may have

Hope this helps !

Thanks & Regards,
Nived N

I think I am getting there, but when using that statement, I get a compiler Error that Rows is not declared.

@mbuehler
Sorry again, i put a comma before Rows(0) instead of .

Just check the previous response, i have corrected it.

I had actually tried that as well and then received this message:

‘Rows’ is not a member of ‘System.Data.EnumerableRowCollection(Of System.Data.DataRow)’.

@mbuehler

Did you happen to try what is provided above?

Cheers

I haven’t tried it yet.

check this

1 Like

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

use this

Cdate( dtTemp.AsEnumerable().Where(Function(row2) row2(“Training Name”).ToString.Trim.Equals(level))(0)(“Training Date”)).ToString(“MM/dd/yyyy”)

1 Like

Perfect, thank you again!

1 Like

You are welcome :slight_smile:

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.