I use an associative array to match item orders to a item master.
The program below collates the order of 50,000 items with the item master of 15,000 items and totals the order amount for each item.
For this degree of quantity, I think that using Excel macro will make a easier system to use, including pre-processing and post-processing ;-)
Code: Select all
'Fast text search (hash table / GLib)
'by TJF ≫ Sep 26, 2011 12:16
'https://www.freebasic.net/forum/viewtopic.php?f=7&t=18558&p=163893&hilit=glib+hash#p163893
' See for details
' http://developer.gnome.org/glib/2.28/glib-Hash-Tables.html
#Include Once "glib.bi" '★１★★★★★★★★★★★
' Create new hash table / Neue HashTable erstellen
' ★新しいハッシュテーブルを作成★
' g_hash_table_new (GHashFunc hash_func, GEqualFunc key_equal_func)
' 演算子 @ (のアドレス)
' g_str_hash (gconstpointer v) 文字列をハッシュ値に変換します。
' g_str_equal (gconstpointer v1, gconstpointer v2) ２つの文字列をバイト毎に比較して、等しい場合は TRUE を返します。
Var MasterItemID = g_hash_table_new(@g_str_hash, @g_str_equal) '★２★★★★★★★★★★★
Var OrderItemID = g_hash_table_new(@g_str_hash, @g_str_equal) '★２★★★★★★★★★★★
Dim STARTT As Long
Dim ENDTIME As Long
Dim Minut As Integer
Dim Shared ItemID(20000) As String
Dim Shared OrderLine(20000) As String
Dim Shared OrderItem As String
Dim Shared Region As String
Dim Shared Price As String
Dim Shared Weight As String
Dim Shared Counter As Integer
Dim Shared collisions As Integer
Dim i As Integer
Dim QuantityString As String
Dim Amount As String
Dim Dimension As Integer
Dim cellString As String
Dim file_name As String
Dim file_num As Integer
Dim CharacterString As String
Dim Regions As Integer
Dim ItemMasterNo As Integer
Dim IntegratedNo As Integer
Dim Orders As Integer
Dim Shared ItemMasterArray(20000,3) As String 'Region, price, weight
Dim Shared IntegratedOrderArray(20000,5) As String 'Region, ItemID, Quantity, Amount, Weight
Dim Shared SortArray(20000) As String
'****************************************************************
'****************************************************************
'★★★多次元配列を複数列を使ってソート★★★
'How to sort datasheet (x-dim array) by a few columns (at once in 1 loop) ?
'by badidea ≫ Dec 15, 2019 21:54
'https://www.freebasic.net/forum/viewtopic.php?f=3&t=27993&start=30#p266914
'by fxm ≫ Jun 25, 2020 6:49
'https://www.freebasic.net/forum/viewtopic.php?f=3&p=273475&sid=547977eb5d3cc9cf3e6ee7323616cff3#p273475
#Include "crt/stdlib.bi"
#Include "string.bi"
'------------------------------- class: row_type -------------------------------
Type row_type
Dim As String col(Any)
Declare Operator Cast () As String
End Type
Operator row_type.cast () As String
Dim As String tempStr
For i As Integer = 1 To UBound(col)
If i = 1 Then tempStr &= col(i) Else tempStr &= !"\t" & col(i)
Next
Return tempStr
End Operator
'------------------------------ class: sort_type -------------------------------
Type sort_type
Dim As Short column
Dim As Short direction
Declare Constructor()
Declare Constructor(column As Short, direction As Short)
End Type
'a stupid constructor
Constructor sort_type()
this.column = 0
this.direction = 0
End Constructor
'another stupid constructor
Constructor sort_type(column As Short, direction As Short)
this.column = column
this.direction = direction
End Constructor
'------------------------------ class: data_type -------------------------------
Type data_type
Static As sort_type sortOrder(1 To 3)
Dim As Integer numRows, numCols
Dim As row_type row(Any)
Declare Constructor(numRows As Integer, numPivotMS As Integer)
Declare Destructor()
Declare Sub copyTo(dst As data_type)
Declare Sub sort(sort1st As sort_type, sort2nd As sort_type, sort3rd As sort_type)
Declare Static Function qSortCallback Cdecl(pRow1 As row_type Ptr, pRow2 As row_type Ptr) As Long
End Type
Dim As sort_type data_type.sortOrder(1 To 3)
Constructor data_type(numRows As Integer, numCols As Integer)
ReDim row(numRows)
this.numCols = numCols
For iRow As Integer = 1 To numRows
ReDim (row(iRow).col)(numCols) 'weird syntax, compiler wants the extra ( )
Next
End Constructor
Destructor data_type()
For iRow As Integer = 1 To numRows
Erase row(iRow).col
Next
Erase row
End Destructor
Sub data_type.sort(sort1st As sort_type, sort2nd As sort_type, sort3rd As sort_type)
'disable invalid sort filters
sortOrder(1) = IIf(sort1st.column < 1 Or sort1st.column >= numCols, sort_type(0,0), sort1st)
sortOrder(2) = IIf(sort2nd.column < 1 Or sort2nd.column >= numCols, sort_type(0,0), sort2nd)
sortOrder(3) = IIf(sort3rd.column < 1 Or sort3rd.column >= numCols, sort_type(0,0), sort3rd)
qsort(@row(1), UBound(row), SizeOf(row_type), CPtr(Any Ptr, @qSortCallback))
End Sub
Function data_type.qSortCallback Cdecl(pRow1 As row_type Ptr, pRow2 As row_type Ptr) As Long
For i As Integer = 1 To 3
With sortOrder(i)
Select Case .direction
Case +1
If pRow1->col(.column) < pRow2->col(.column) Then Return -1
If pRow1->col(.column) > pRow2->col(.column) Then Return +1
Case -1
If pRow1->col(.column) > pRow2->col(.column) Then Return -1
If pRow1->col(.column) < pRow2->col(.column) Then Return +1
Case Else
'skip, including direction = 0
End Select
End With
Next
Return 0
End Function
'****************************************************************
'****************************************************************
Print "Read ""ItemMaster.csv"" and store each line in ItemMasterArray, indexing ItemID."
'****************************************************************
Print
file_name = "ItemMaster.csv"
file_num = FreeFile( ) '' 有効なファイル番号を検索します
'' ファイルを開きます。そして、ファイル番号をそれに結び付けます。エラーが有れば、抜けます。
If( Open( file_name For Input As #file_num ) ) Then
Print "ERROR: 開こうとしたファイル名 " ; file_name
Sleep
End -1
End If
Counter = 0
collisions = 0
Do Until Eof( file_num ) '' ファイルの端に達するまで、繰り返します。
Counter = Counter + 1
ItemID(Counter) ="": Region ="": Price ="" : Weight =""
Line Input #file_num, CharacterString '' テキストの行を読みます。
ItemID(Counter) = Left(CharacterString,3)
Region = Mid(CharacterString,6,InStrRev(CharacterString,"""")-6)
Price = Mid(CharacterString,InStrRev(CharacterString,"""")+2,InStrRev(CharacterString,",")-InStrRev(CharacterString,"""")-2)
Weight = Right(CharacterString,Len(CharacterString)-InStrRev(CharacterString,","))
If g_hash_table_lookup(MasterItemID, StrPtr(ItemID(Counter))) = 0 Then '★４★★★★★★★★★★★
'g_hash_table_insert (GHashTable *hash_table, gpointer key, gpointer value)
'GLib には、int から gpointer へ正確にキャストする GINT_TO_POINTER というマクロと、
'gpointer から int へ正確にキャストする GPOINTER_TO_INT というマクロが定義されています。
g_hash_table_insert(MasterItemID, StrPtr(ItemID(Counter)), GINT_TO_POINTER(Counter)) '★３★★★★★★★★★★★
ItemMasterArray(Counter,1) = Region
ItemMasterArray(Counter,2) = Price
ItemMasterArray(Counter,3) = Weight
Else
'キーの重複が発生したら表示する
'Display when duplicate keys occur
collisions += 1
Print
Print g_hash_table_lookup(MasterItemID, StrPtr(ItemID(Counter))) , Counter, ItemID(Counter)
Sleep
End If
Loop
Print "Numbers of Item Master = ";Counter
Print
Print "Contents of the last Item Master : ";CharacterString ' 画面に最終行を出力します。
Print ItemID(Counter) , Region , Price , Weight
Print
Close #file_num '' ファイル番号を通したファイルを閉じます。
'****************************************************************
'****************************************************************
Print "Read ""OrderList.csv"" and compare it with ItemMasterArray to aggregate price and weight by item and region."
'****************************************************************
Print
Open "OrderItemError.csv" For Output As #1
file_name = "OrderList.csv"
file_num = FreeFile( ) '' 有効なファイル番号を検索します
'' ファイルを開きます。そして、ファイル番号をそれに結び付けます。エラーが有れば、抜けます。
If( Open( file_name For Input As #file_num ) ) Then
Print "ERROR: 開こうとしたファイル名 " ; file_name
Sleep
End -1
End If
Counter = 0
Orders = 0
STARTT=Val(Left(Time,2))*3600+Val(Mid(Time,4,2))*60+Val(Right(Time,2))
Do Until Eof( file_num ) '' ファイルの端に達するまで、繰り返します。
CharacterString = "" : OrderItem = "" : QuantityString = ""
Line Input #file_num, CharacterString '' テキストの行を読みます。
Orders = Orders + 1
OrderItem = Left(CharacterString,3)
QuantityString = Right(CharacterString,Len(CharacterString)-InStrRev(CharacterString,","))
If g_hash_table_lookup(MasterItemID, StrPtr(OrderItem)) = 0 Then '★４★★★★★★★★★★★
'Print OrderItem, g_hash_table_lookup(MasterItemID, StrPtr(OrderItem))
'Sleep
'エラー出力
Print #1, CharacterString
Else
ItemMasterNo = g_hash_table_lookup(MasterItemID, StrPtr(OrderItem))
'ItemMasterArray(20000,3) 'Region, price, weight
'IntegratedOrderArray(20000,5) 'Region, ItemID, Quantity, Amount, Weight
Region = ItemMasterArray(ItemMasterNo,1)
Amount = Str(Val(QuantityString)*Val(ItemMasterArray(ItemMasterNo,2)))
Weight = Str(Val(QuantityString)*Val(ItemMasterArray(ItemMasterNo,3)))
If g_hash_table_lookup(OrderItemID, StrPtr(OrderItem)) = 0 Then '★４★★★★★★★★★★★
Counter = Counter + 1
OrderLine(Counter) = OrderItem
g_hash_table_insert(OrderItemID, StrPtr(OrderLine(Counter)), GINT_TO_POINTER(Counter)) '★３★★★★★★★★★★★
IntegratedOrderArray(Counter,1) = Region
IntegratedOrderArray(Counter,2) = OrderItem
IntegratedOrderArray(Counter,3) = QuantityString
IntegratedOrderArray(Counter,4) = Amount
IntegratedOrderArray(Counter,5) = Weight
'Print Counter,Region,ItemID,QuantityString,Amount,Weight
'sleep
Else
IntegratedNo = g_hash_table_lookup(OrderItemID, StrPtr(OrderItem))
IntegratedOrderArray(IntegratedNo,3) = Str(Val(IntegratedOrderArray(IntegratedNo,3))+Val(QuantityString))
IntegratedOrderArray(IntegratedNo,4) = Str(Val(IntegratedOrderArray(IntegratedNo,4))+Val(Amount))
IntegratedOrderArray(IntegratedNo,5) = Str(Val(IntegratedOrderArray(IntegratedNo,5))+Val(Weight))
'Print IntegratedNo,ItemID,QuantityString,Amount,Weight
'sleep
End If
End If
Loop
Print "Number of orders in the list : ";Orders
Print
ENDTIME = Val(Left(Time,2))*3600+Val(Mid(Time,4,2))*60+Val(Right(Time,2))
Minut=(ENDTIME-STARTT)\60
Print Using "processing time: ## minutes ## seconds"; Minut; (ENDTIME-STARTT)-Minut*60
Print
Close #1
Close #file_num '' ファイル番号を通したファイルを閉じます。
'****************************************************************
'****************************************************************
' Destroy table, release memory / Tabelle aufloesen, Speicher freigeben
' テーブルを破棄、メモリを解放
g_hash_table_unref(MasterItemID)
g_hash_table_unref(OrderItemID)
'****************************************************************
'****************************************************************
Print "Sort the totaled results by Region and ItemID."
'****************************************************************
Print
Dim As Integer numRows = Counter, numCols = 5
Var myData = data_type(numRows, numCols)
For iRow As Integer = 1 To Counter
With myData.row(iRow)
For iCol As Integer = 1 To UBound(.col)
.col(iCol) = IntegratedOrderArray(iRow,iCol)
Next
End With
Next iRow
myData.sort(sort_type(1, +1), sort_type(2, +1), sort_type(0, 0))
For iRow As Integer = 1 To Counter
With myData.row(iRow)
For iCol As Integer = 1 To UBound(.col)
IntegratedOrderArray(iRow,iCol) = .col(iCol)
Next
End With
Next iRow
'****************************************************************
'****************************************************************
Print "Out put the sorted results to ""OrderSorting.csv"""
'****************************************************************
Print
'IntegratedOrderArray(20000,5) 'Region, ItemID, Quantity, Amount, Weight
Open "OrderSorting.csv" For Output As #1
For i = 1 To Counter
CharacterString = ""
For Dimension =1 To 5
cellString = IntegratedOrderArray(i,Dimension)
If Dimension = 1 Then
cellString = """" & cellString & """"
EndIf
If Dimension > 1 Then
cellString = "," & cellString
EndIf
CharacterString = CharacterString & cellString
Next Dimension
Print #1, CharacterString
Next i
Close #1
'****************************************************************
'****************************************************************
Print "Output of the sorted order aggregate has been completed."
Print "*******************************************************"
Print "Please enter any key to exit."
Sleep
