Grouping data with arrays

I have 2 related arrays of data. I would like to list the sales, grouped by category id.
I can figure out how to group the categories on 3 lines, but I can’t determine how to group the sales with the categories. I’m thinking I have the wrong approach here. Any thoughts?

Var CategoryID() As String = Array("3", "1", "3", "2", "1", "2", "3", "2") 

Var Sales() As Double = Array(20.00, 10.00, 30.00, 25.00, 15.00, 10.00, 20.00, 8.00)  

CategoryID.SortWith(Sales)

Var LastCategory As String
Var CategorySales As Double

Var SalesByCategory() As Double
Var CategoryGroup() as String

For i as Integer = 0 to CategoryID.LastIndex
  if CategoryID(i) <> LastCategory then
    CategoryGroup.Add(CategoryID(i))
  end if
  LastCategory = CategoryID(i)
next

// Test Results
For i as Integer = 0 to CategoryGroup.LastIndex
  System.DebugLog "CategoryGroup = " + CategoryGroup(i)
next

I figured it out.

Var CategoryID() As String = Array("3", "1", "3", "2", "1", "2", "3", "2") 

Var Sales() As Double = Array(20.00, 10.00, 30.00, 25.00, 15.00, 10.00, 20.00, 8.00)  

CategoryID.SortWith(Sales)

Var LastCategory As String
Var CategorySales As Double

Var SalesByCategory() As Double
Var CategoryGroup() as String

Var LastIdx As Integer

For i as Integer = 0 to CategoryID.LastIndex
  if CategoryID(i) <> LastCategory then
    SalesByCategory.Add(Sales(i))
    CategoryGroup.Add(CategoryID(i))
    CategorySales = Sales(i)
  else
    CategorySales = CategorySales + Sales(i)
    SalesByCategory(SalesByCategory.LastIndex) = CategorySales
  end if
  LastCategory = CategoryID(i)
next

// Test Result
For i as Integer = 0 to CategoryGroup.LastIndex
  System.DebugLog "CategoryGroup = " + CategoryGroup(i) + " Sales = " + SalesByCategory(i).ToString
next

// CategoryGroup = 1 Sales = 25
// CategoryGroup = 2 Sales = 43
// CategoryGroup = 3 Sales = 70



This is exactly what Dictionaries are for. There’s no need to sort the incoming data because the code doesn’t depend on the order of the input, and the code is more understandable as a result.

Var CategoryID() As String = Array("3", "1", "3", "2", "1", "2", "3", "2") 

Var Sales() As Double = Array(20.00, 10.00, 30.00, 25.00, 15.00, 10.00, 20.00, 8.00)  

Var CategoryToSalesMap as New Dictionary

Var currentCategoryID as String
Var currentSales as Double

For i as Integer = 0 To CategoryID.LastIndex
    currentCategoryID = CategoryID(i)
    currentSales = Sales(i)

    If CategoryToSalesMap.HasKey(currentCategoryID) Then
        CategoryToSalesMap.Value(currentCategoryID) = currentSales
    Else
        CategoryToSalesMap.Value(currentCategoryID) = CategoryToSalesMap.Value(currentCategoryID) + currentSales
    End
Next

'Show test results
Var currentEntry as DictionaryEntry

For Each currentEntry in CategoryToSalesMap
    SystemDebugLog "Category Group = " + currentEntry.Key + " Sales = " + currentEntry.Value
Next

'Individual values
SystemDebugLog "Category 2 Sales: " + CategoryToSalesMap.Value(2)
1 Like

// The Memory DB helper way
// Best when you want to extract different views from the same dataset

Var catsale As New SQLiteDatabase
catsale.Connect

catsale.ExecuteSQL("CREATE TABLE mem (cat TEXT, sale REAL);")
catsale.ExecuteSQL("CREATE INDEX memcat ON mem (cat);")

Var insertValue As String = "INSERT INTO mem VALUES (?, ?);"

catsale.ExecuteSQL(insertValue, "3", 20.00)
catsale.ExecuteSQL(insertValue, "1", 10.00)
catsale.ExecuteSQL(insertValue, "3", 30.00)
catsale.ExecuteSQL(insertValue, "2", 25.00)
catsale.ExecuteSQL(insertValue, "1", 15.00)
catsale.ExecuteSQL(insertValue, "2", 10.00)
catsale.ExecuteSQL(insertValue, "3", 20.00)
catsale.ExecuteSQL(insertValue, "2", 08.00)

Var rs As RowSet = catsale.SelectSQL("SELECT cat, SUM(sale) As sales FROM mem GROUP BY cat ORDER BY cat ;")

For Each row As DatabaseRow IN rs
  System.DebugLog "Category: " + row.Column("cat").StringValue +_
   " , Sales: " + row.Column("sales").DoubleValue.ToString("#,##0.00;-#,##0.00")
Next

app.DoEvents() // Print debug messages and quit
Quit

// Debug Outputs:

//         : Category: 1 , Sales: 25.00
//         : Category: 2 , Sales: 43.00
//         : Category: 3 , Sales: 70.00

I knew that there had to be a cleaner way of doing this. Thank you, Eric, for providing it.