Converting Rows in columns

byka

I have a datatable where data represented like this ID Code Effective Date 111 103 01/01/2015 111 104 01/01/2014 111 105 01/01/2013 111 103 01/01/2014 111 103 01/01/2013 111 103 01/01/2012 112 103 01/01/2012 112 103 01/01/2013 I need to create a new table with the latest 3 effective date per each ID and Code: Result: ID Code Effective Date1 Effective Date2 Effective Date3 111 103 01/01/2015 01/01/2014 01/01/2013 111 104 01/01/2014 111 105 01/01/2013 112 103 01/01/2013 01/01/2012 How do I do this? I have started with cloning the table structure(myDataTable already have Columns: Effective Date1 Effective Date2 Effective Date3) Dim copyDataTable As DataTable copyDataTable = myDataTable.Clone()

Richard Deeming

A spot of LINQ makes this relatively easy:

Dim data = myDataTable.AsEnumerable().GroupBy(Function (r) New With
{
Key .ID = r.Field(Of Integer)("ID"),
Key .Code = r.Field(Of Integer)("Code")
},
Function (key, rows) New With
{
Key .ID = key.ID,
Key .Code = key.Code,
.Dates = rows.Select(Function (r) r.Field(Of DateTime?)("Effective Date")) _
.OrderByDescending(Function (d) d).Take(3).ToList()
})

Dim result As New DataTable()
result.Columns.Add("ID", GetType(Integer))
result.Columns.Add("Code", GetType(Integer))
result.Columns.Add("Effective Date 1", GetType(DateTime))
result.Columns.Add("Effective Date 2", GetType(DateTime))
result.Columns.Add("Effective Date 3", GetType(DateTime))

For Each item in data
result.Rows.Add(item.ID, item.Code,
item.Dates.ElementAtOrDefault(0),
item.Dates.ElementAtOrDefault(1),
item.Dates.ElementAtOrDefault(2))
Next

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

byka

I have added Imports System.Linq to my declaration however Getting errors below: 'AsEnumerable' is not a member of 'System.Data.DataTable'. item' is not declared. It may be inaccessible due to its protection level

Richard Deeming

Make sure you have a reference to the System.Data.DataSetExtensions assembly, and an Imports System.Data declaration at the top of your file.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

byka

I have added all references however still the following lines won't compile. Option Strict On requires all variable declarations to have an 'As' clause.

Dim data = myDataTable.AsEnumerable().GroupBy(Function(r) New With {Key .ID = r.Field(Of Integer)("ID"), Key .Code = r.Field(Of Integer)("Code")},

and 'item' is not declared. It may be inaccessible due to its protection level.

For Each item In data

Richard Deeming

You're using an anonymous type, so you can't give the variables an explicit type. Try setting Option Infer On, which should allow the compiler to infer the type for you.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

byka

it worked. Now I am facing different issue when adding a new row into new table with non key columns

result.Rows.Add .... item.EENAML.. etc.

Could you tell me what am I doing wrong? Here my full code

Dim data = myDataTable.AsEnumerable().GroupBy(Function(r) New With {Key .EEGRP = r.Field(Of String)("EEGRP"), Key .EESSN = r.Field(Of String)("EID")},
Function(key, rows) New With
{
Key .EEGRP = key.EEGRP,
Key .ESSN = key.EID,
.Dates = rows.Select(Function(r) r.Field(Of DateTime?)("DEIBCY") & ("DIIBYR") & ("D.DIIBMT") & ("DEIBDY")) _
.OrderByDescending(Function(d) d).Take(3).ToList()
})

                 'Create new table with original table schema
                 Dim result As New DataTable
                 result = myDataTable.Clone()
                'Create new  row
                 For Each item In data
                     result.Rows.Add(item.EEGRP,item.EID, item.EENAML,item.EENAMF,item.EENAMM,item.EEADD1,item.EEADD2,,,, \_

Richard Deeming

What's the error message?

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

byka

'FIELD' is not a member of ''; it does not exist in the current context.

Richard Deeming

Make sure you have a reference to the System.Data.DataSetExtensions assembly, and an Imports System.Data declaration at the top of your file. The Field(Of T) method is an extension method defined in that namespace and assembly: DataRowExtensions.Field<T> Method (DataRow, String)[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

byka

When I add

Imports System.Data.DataSetExtensions
Module ModuleMain...

it saying it can't be found. I do have reference added to the project.

Richard Deeming

The assembly is System.Data.DataSetExtensions; the namespace is System.Data.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

byka

When I change my project properties to .net 3.5 I was able to add Imports System.Data.DataSetExtensions however even with that I still having issues with no key fields for selecting is not a member of ''; it does not exist in the current context

For Each item In data
result.Rows.Add(item.EEGRP,item.ESSN, item.EENAML,item.EENAMF,item.EENAMM,item.EEADD1,item.EEADD2,...._