Faster than nested For loops?

[DrewSS]

Hello,

I'm trying to normalize data from 4 different multi-dimension arrays, but they are extremely large and I'm trying to learn a faster way of parsing data. Please note I am rather newb at programming.

 

The following script does exactly what I need, but its really slow. Is there a better way to parse data than nested For loops?

 

Basically the logic is:

1.       For All store/lanes in the Quarterly report, find any matches in Current day’s Lane issues 

2.       (if any), then if lane issues exist and they are in the quarterly report, find match in Revenue report

3.       (if any), then if lane issues exist and they are in the quarterly report, that have contract values from Revenue report, then perform calculations and output It

 

  I cannot provide the documents due to sensitive data.

  Quarterly Report array is about 80,000 Rows and 13 Columns

  Store/lane Issues array is about 25,000 Rows and 3 Columns

  Revenue array report is about 100 Rows and 13 Columns

  National Store/Retailer translation array is about 30,000 Rows and 3 Columns

 

  Current output takes roughly 0.5 seconds per cycle, so it would be about 11 hours for all 80,000 rows.

 

  Any help or advice would be greatly appreciated!!

 

 

expandcollapse popup

$date = StringReplace(_NowCalcDate(), "/", "-")
$ROI_output = @ScriptDir & "\ROI_output_" & $date & ".txt"
$ROI_output1 = FileOpen($ROI_output, 1)
FileWrite($ROI_output1, "Retailer" & " | " & "Chain-Store" & " | " & "Store Value" & " | " &  "Lane" & " | " & "Lane Value"  & " | " & "Status"  & " | " & "StoreWeight" & " | " & "LaneWeight" & " | " & "RetailerValue " & @CRLF)

;Quarterly report
$lanestoreweights = "Z:\STAT\lanestoreweights.xlsx"
Local $oApplD2 = _Excel_Open(False);
Local $oExcelD2 = _Excel_BookOpen($oApplD2, $lanestoreweights)
Local $lanestoreweightsarray = _Excel_RangeRead($oExcelD2, Default, $oExcelD2.ActiveSheet.Usedrange.Columns("C:M"), 1, True)
_Excel_BookClose($oApplD2)

;Current Day's lane issues
$storelanestatuses = "Z:\STAT\Store_Lane_values-" & $date & ".txt"
Local $storelanearray
_FileReadToArray($storelanestatuses, $storelanearray)

;Revenue Report
$rev =  "Z:\STAT\OI_Revenue_Report_PipeDelimited.txt"
Local $revarray
_FileReadToArray($rev, $revarray)

;Chain to Retailer translation
Local $nationallocal = "Z:\STAT\national.xls"
Local $oApplD1 = _Excel_Open(False)
Local $oExcelD1 = _Excel_BookOpen($oApplD1, $nationallocal)
Local $livestores = _Excel_RangeRead($oExcelD1, Default, $oExcelD1.ActiveSheet.Usedrange.Columns("A:C"), 1, True)
_Excel_BookClose($oApplD1)



For $z = 2 To UBound($lanestoreweightsarray) -1
    $split2 = StringSplit($lanestoreweightsarray[$z][0], "-")
    If UBound($split2) > 3 Then
      $wtChain = StringFormat("%03i", $split2[1])
      $wtStore = StringFormat("%04i", $split2[2])
        $wtLane = StringFormat("%02i", $split2[3])
     EndIf

        $storeweight1 = ""
        $laneweight1 = ""
        $rev_retailer = ""
        $rev_val = ""
        For $x = 1 To UBound($storelanearray) -1
            $split = StringSplit($storelanearray[$x], " ")
            $chain = $split[3]
            $store = $split[4]
            $lane = $split[6]
            $statfilesplit = StringSplit($storelanearray[$x], "***")
            $lanestatus = $statfilesplit[4]

            If $chain = $wtChain  And $store = $wtStore And $lane = $wtLane Then
                $storeweight = $lanestoreweightsarray[$z][9]
                $storeweight1 = $storeweight * 100
                $laneweight = $lanestoreweightsarray[$z][10]
                $laneweight1 = $laneweight * 100
                
                For $y = 1 To UBound($livestores) -1
                    If $chain = StringFormat("%03i", $livestores[$y][2]) Then
                        If $livestores[$y][0] = "No Chain Group Assigned." Then
                            $retailer =  $livestores[$y][1]
                            ExitLoop
                        Else
                            $retailer =  $livestores[$y][0]
                            ExitLoop
                        EndIf
                    EndIf
                Next

            For $r = 2 To UBound($revarray) -1
                $split3 = StringSplit($revarray[$r], "|")

                If $rev_retailer = "" Then
                    If $split3[1] = "" Then
                        ;ConsoleWrite($retailer & "---" & $split3[3] & @CRLF)
                        If $split3[3] = $retailer Then
                            $rev_retailer = $split3[3]
                            $rev_val = StringLeft($split3[13], (StringLen($split3[13]) - 2))
                            ExitLoop
                        EndIf

                    Else
                        ;ConsoleWrite($retailer & "-" & $split3[1] & @CRLF)
                        If $split3[1] = $retailer Then

                            $rev_retailer = $split3[1]
                            $rev_val = StringLeft($split3[13], (StringLen($split3[13]) - 2))
                            ExitLoop
                        EndIf

                    EndIf
                EndIf
            Next
            
            If $rev_val = "" Then
                ContinueLoop
            Else
                $store_value = $rev_val * $storeweight1
                $printer_value = $store_value * $laneweight1
                ConsoleWrite($retailer & " | " & $chain & "-" & $store & " | " & "Store Value: " & $store_value & " | " &  "Lane: " & $lane & " | " & "Lane Value: " & $printer_value  & " | " & "Status: " & $lanestatus & " | " & "StoreWeight: " & $storeweight1 & " | " & "LaneWeight: " & $laneweight1 & " | " & "RetailerVal: " & $rev_val & @CRLF)
                FileWrite($ROI_output1, $retailer & " | " & $chain & "-" & $store & " | " & $store_value & " | " &  $lane & " | " & $printer_value  & " | " & $lanestatus & " | " &  $storeweight1 & " | " &  $laneweight1 & " | " & $rev_val & @CRLF)
            EndIf
    EndIf
    Next
Next

 

[MilesAhead]

I haven't used SQLite myself.  But I would think it a more efficient tool than scanning arrays by hand.  Once you figure out how to import the excel data into the SQLite database you could run queries to generate the reports instead of rolling your own.

I would search for any SQLite examples esp. involving excel.

 

Edited November 3, 2015 by MilesAhead

[DrewSS]

Right on; I dont have much SQL experience but gotta learn some time. 

 

[LarsJ]

Take a look at this post. The optimizations in this post are based on sorted arrays where you search the rows with a binary search algorithm. If your data can be sorted in the same way, so that it's possible to use binary search, your For-loops can be very optimized. If your arrays can be properly sorted it should be possible to reduce the 11 hours to a few minutes.

If it's possible for you to produce some test data with about 5% of the amount of rows, then I'll try to optimize the For-loops. It's important that the test data are relatively realistic in terms of sorting options.

[DrewSS]

Thank you LarsJ! Thats an excellent suggestion and great link. I'll provide some example files later today then try to practice sorting.

 

Thank you again!

 

 

[junkew]

Vlookup in excel is probably more effective as your data seems to be in excel. And excel also has data query toolbar where you can join your ranges.