I have a DataTable with the below data which I'm wanting to format and just have the most recent weeks data.
ID | Name | Week 1 | Week 2 | Week 3 | Week 4 |
---|---|---|---|---|---|
1 | Conor | 100 | 87 | 3 | 0 |
2 | Frank | 35 | 70 | 0 | 0 |
3 | Jeff | 35 | 13 | 0 | 57 |
I would like to keep the first 2 columns and then keep the right most column that isn't 0 giving me the following
ID | Name | Value |
---|---|---|
1 | Conor | 3 |
2 | Frank | 70 |
3 | Jeff | 57 |
I'm quite new to LINQ so I'm a little unsure if it's possible to do this or not so any help would be appreciated.
Additional Info: I forgot to mention that I'm creating the solution in UiPath (an RPA tool) so although VB Code would be better for this instance LINQ is preferable.
CodePudding user response:
Linq is cool but working code is even cooler. Linq isn't necessarily faster. It does the loops internally.
Your code in the GetDataTable
function would be the extraction of the data from Excel. I just built a DataTable to match your example.
In the button click event I created a table to hold the result. The outer loop goes through each row in the source DataTable
. The inner For
loop starts at the right most column in the dtSource
and steps back to the third column (index 2). Note the Step -1
. This should work for any number of Week columns since we use dtSource.Columns.Count - 1
As soon as it finds an non zero value it adds a record to dtResult
and exits the inner For
going on to the next row in dtSource
.
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim dtSource = GetDataTable()
Dim dtResult As New DataTable
dtResult.Columns.Add("ID", GetType(Integer))
dtResult.Columns.Add("Name", GetType(String))
dtResult.Columns.Add("Value", GetType(Integer))
For Each row As DataRow In dtSource.Rows
For i = dtSource.Columns.Count - 1 To 2 Step -1
If CInt(row(i)) <> 0 Then
dtResult.Rows.Add({row("ID"), row("Name"), row(i)})
Exit For
End If
Next
Next
DataGridView1.DataSource = dtResult
End Sub
Private Function GetDataTable() As DataTable
Dim dt As New DataTable
dt.Columns.Add("ID", GetType(Integer))
dt.Columns.Add("Name", GetType(String))
dt.Columns.Add("Week1", GetType(Integer))
dt.Columns.Add("Week2", GetType(Integer))
dt.Columns.Add("Week3", GetType(Integer))
dt.Columns.Add("Week4", GetType(Integer))
dt.Rows.Add({1, "Conor", 100, 87, 3, 0})
dt.Rows.Add({2, "Frank", 35, 70, 0, 0})
dt.Rows.Add({3, "Jeff", 35, 13, 0, 57})
Return dt
End Function