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.