Getting data out of dataset

[danielgr]

I have a dataset with the following data:

account_number,master_code,score
100000023,UL189PU,1
100000025,UK120PU,1
100000026,AAL004PU,1
100000026,UL221PU,1
100000026,YHL003PU,1
100000034,UL246PP,1
100000043,1198112CS,1
100000060,182112JS,1
100000060,996211CS,1
100000060,AAL012PU,1
100000074,UI136PU,1
100000074,UK131PU,1
100000074,UL178SU,2

I've started a master loop where I loop through the accounts.

I now need to do a sub loop and compare this account to the rest of the accounts in the dataset and to check whether the item_codes match or not.
i.e.

If account_number_master_loop = account_number_sub_loop Then
    If item_code_master_loop = item_code_sub_loop Then
        'Not sure what type of variable this should be
        variable = account_number_sub_loop,item_code_sub_loop,score
    Else
        variable = account_number_sub_loop,item_code_sub_loop,0
    End If
End If

What would be the best way to achieve this?

Thanks.

[daniel955]

I'm finding it hard to understand the problem, so here is my question

• Are those data retrieved from some database?,
  how are they inserted in the first place?
• For what purposes do you check for duplicates?

[danielgr]

The data originally comes from a database.

I check for duplicates with the original loop as I don't want to check the same account number twice.

What I'm trying to eventually do is compare the accounts. So, take all the items which are similar to both accounts and get a score. For example let's say account 1 bought item_a and account 2 bought item_a and item_b, they would have a score of:
item_a: 0+1
item_b: 1+1
total: 3

[daniel955]

Hmm, it looks something like a sales report so,
instead of retrieving all the data from the database.
you can get your desired output via sqlquery

example query:

Select master_code, sum(score)
from tablename
group by master_code

Note: Replace tablename with your actual table name

What this query does is group your table by the itemcode (master_code)
and display the total of the quantity sold (score).
Which is exactly what you want. Try it . . .

and the resulting query will generate a table/dataset that looks something like:

itemcode,quantity
item_a,1
item_b,2
item_c,3

Hope this helps.

[danielgr]

Thank you!

As a matter of interest, do you know if there is a way to do a similar query on a dataset using LINQ?

[daniel955]

Sorry I have no experience in LINQ.

[TnTinMN]

As a matter of interest, do you know if there is a way to do a similar query on a dataset using LINQ?

You would have to run Linq against the DataTable.Rows collection. You could also use the DataTable.Compute method for a non-Linq solution.

   Dim dt As New DataTable
   Dim r As DataRow
   'make some data to work with
   With dt
      .TableName = "fred"
      .Columns.Add(New DataColumn("Account", GetType(Int32)))
      .Columns.Add(New DataColumn("master code", GetType(String)))
      .Columns.Add(New DataColumn("score", GetType(Int32)))
      r = .NewRow : r(0) = 100 : r(1) = "code1" : r(2) = 1 : .Rows.Add(r)
      r = .NewRow : r(0) = 101 : r(1) = "code1" : r(2) = 1 : .Rows.Add(r)
      r = .NewRow : r(0) = 103 : r(1) = "code1" : r(2) = 1 : .Rows.Add(r)
      r = .NewRow : r(0) = 105 : r(1) = "code3" : r(2) = 1 : .Rows.Add(r)
      r = .NewRow : r(0) = 300 : r(1) = "code3" : r(2) = 32 : .Rows.Add(r)
      .AcceptChanges()
   End With
   'Put it in a dataset, since that was what was asked for
   Dim ds As New DataSet
   ds.Tables.Add(dt)

   Dim searchcode As String = "code3" 'something to seek

   'Option 1: DatatTable.Compute method
   Dim total1 As Int32 = CType(ds.Tables("fred").Compute("Sum([score])", "[master code]='" & searchcode & "'"), Int32)

   'Option 2: Use Linq Extensions
   'Note:  I despise Anonymous Types and Inference, so I use typed Linq
   Dim scores As IEnumerable(Of Int32) = From row In ds.Tables("fred").Rows Where CType(CType(row, DataRow).Item("master code"), String) = searchcode Select CType(CType(row, DataRow).Item("score"), Int32)
   Dim total2 As Int32 = scores.Sum

   'Option 3: Option2 in one step
   Dim total3 As Int32 = (From row In ds.Tables("fred").Rows Where CType(CType(row, DataRow).Item("master code"), String) = searchcode Select CType(CType(row, DataRow).Item("score"), Int32)).Sum