Sign in to follow this  
Followers 0
DrewSS

Faster than nested For loops?

6 posts in this topic

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!!

 

 

$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

 

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

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 by MilesAhead
1 person likes this

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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.

1 person likes this

Share this post


Link to post
Share on other sites

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!

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • NiftRex
      By NiftRex
      I'm trying to get an array from a website so that I can just get the url, but I am not sure how. I read a bit of arrays but I have a feeling I'd have to be writing a lot more than what I should be. I will include the script I have so far and the API url for what I want.
       
      API: https://api.fast.com/netflix/speedtest?https=true&token=YXNkZmFzZGxmbnNkYWZoYXNkZmhrYWxm&urlCount=1 (I want the 'url' array that contains the url)
       
      Code:
      #include <MsgBoxConstants.au3> #include <Inet.au3> #include <Array.au3> $site = _INetGetSource('http://api.fast.com/netflix/speedtest?https=true&token=YXNkZmFzZGxmbnNkYWZoYXNkZmhrYWxm&urlCount=1') MsgBox($MB_SYSTEMMODAL, "Title", $site[1])  
    • rcmaehl
      By rcmaehl
      Alright, currently I'm trying to merge two loops:
       
      Local $aArray[99][13] For $iLoop = 1 to 100 $aArray[$iLoop - 1][0] = RegEnumKey("HKLM\SYSTEM\CurrentControlSet\Control\GraphicsDrivers\Configuration", $iLoop) If @error <> 0 Then ReDim $aArray[$iLoop - 1][13] ExitLoop EndIf Next For $iLoop1 = 0 to UBound($aArray, 1) - 1 For $iLoop2 = 1 to 12 $aArray[$iLoop1][$iLoop2] = RegEnumKey("HKLM\SYSTEM\CurrentControlSet\Control\GraphicsDrivers\Configuration\" & $aArray[$iLoop1][0], $iLoop2) If @error <> 0 Then ExitLoop Next Next  
      into one. Here's what I have so far:
       
      Local $aArray[99][13] For $iLoop1 = 0 to UBound($aArray, 1) - 1 For $iLoop2 = 0 to 12 ConsoleWrite("Loop1 = " & $iLoop1 & ", Loop2 = " & $iLoop2 & @CRLF) If $iLoop2 = 0 Then $aArray[$iLoop1][0] = RegEnumKey("HKLM\SYSTEM\CurrentControlSet\Control\GraphicsDrivers\Configuration", $iLoop1 + 1) If @error <> 0 Then ReDim $aArray[$iLoop1 + 1][13] ExitLoop EndIf Else $aArray[$iLoop1][$iLoop2] = RegEnumKey("HKLM\SYSTEM\CurrentControlSet\Control\GraphicsDrivers\Configuration\" & $aArray[$iLoop1][0], $iLoop2) If @error <> 0 Then ExitLoop EndIf Next Next And it keeps error-ing out trying to exceed the dimension range of the array. Does the Loop not recheck the size of the array after the original check or am I doing something else wrong.
       
      Thanks.
    • FrancescoDiMuro
      By FrancescoDiMuro
      Hi guys! How are you? Hope you're fine
      I'm trying to use SQLite for managing some data, and, I would like to display my "retrieved" data, but I'm trying to do a select from 2 table, and seems to not work properly...
      What I'd like to do is retrieve data from 2 tables and display in a listview...
      I tried with:
      Local $aRisultato, $iRighe, $iColonne, $iRVal $iRval = _SQLite_GetTable2d($hDatabase, "SELECT * FROM Magazzino_Rockwell, Magazzino_Siemens;", $aRisultato, $iRighe, $iColonne) If $iRVal = $SQLITE_OK Then ;_SQLite_Display2DResult($aRisultato) _ArrayDisplay($aRisultato) EndIf ... but it displays a single record 41 times, and it does this thing for every record in the database...
      What do you need to help me?
      Thanks  

      EDIT:
      Managed with a double If and double query like this:
       
      $iRval = _SQLite_GetTable2d($hDatabase, "SELECT * FROM Magazzino_Rockwell;", $aRisultato, $iRighe, $iColonne) If $iRVal = $SQLITE_OK Then Local $aRisultatoRockwell = $aRisultato $iRval = _SQLite_GetTable2d($hDatabase, "SELECT * FROM Magazzino_Siemens;", $aRisultato, $iRighe, $iColonne) If $iRVal = $SQLITE_OK Then _ArrayConcatenate($aRisultato, $aRisultatoRockwell) Local $i, $sRiga, $s_LV_Item For $i = 1 To Ubound($aRisultato) - 1 $sRiga = $aRisultato[$i][0] & "|" & _ $aRisultato[$i][1] & "|" & _ $aRisultato[$i][2] & "|" & _ $aRisultato[$i][3] & "|" & _ $aRisultato[$i][4] & "|" & _ $aRisultato[$i][5] & "|" & _ $aRisultato[$i][6] & "|" & _ $aRisultato[$i][7] & "|" & _ $aRisultato[$i][8] & "|" & _ $aRisultato[$i][9] & "|" & _ $aRisultato[$i][10] & "|" & _ $aRisultato[$i][11] $s_LV_Item = GUICtrlCreateListViewItem($sRiga, $lv_Lista) Next And so, the listview is created
      If anyone has another more efficient way, I'm here Thanks guys  
       
    • TheAutomator
      By TheAutomator
      Hi people,
      It's been a while
      I have been scripting in vbscript for some time but now i'm back using AutoIt for a project:
      Scripting Adobe Illustrator with AutoIt .
      This vb code needs to be converted:
      Set appRef = CreateObject("Illustrator.Application") 'Create a new document and assign it to a variable Set documentRef = appRef.Documents.Add 'Create a new text frame item and assign it to a variable Set sampleText = documentRef.TextFrames.Add 'Set the contents and position of the TextFrame sampleText.Position = Array(200, 200) sampleText.Contents = "Hello World!" The ... = Array(200,200) part is done in one line in vb, is this possible in AutoIt without declaring an array first?
      I know I sound like a newbie but I never had to use arrays this way before..
      what i have:
      $appRef = ObjCreate("Illustrator.Application") ;Create a new document and assign it to a variable $documentRef = $appRef.Documents.Add ;Create a new text frame item and assign it to a variable $sampleText = $documentRef.TextFrames.Add ;Set the contents and position of the TextFrame ReDim $sampleText.Position[2] = [200, 200] <<<--- doesn't work... $sampleText.Contents = "Hello World!"  
    • Dent
      By Dent
      I just can't get my head around what I'm trying to do.
      Ok so I read all the links in a webpage into a 1D array, I then make the array unique as there are some duplicates.
      I then find all the entries I want from the array and put the number of the row for those entries into another 1D array.
      So I now have two arrays; one has unique links, the other has the numbers of the rows for the links I actually want.
      Using the second array I would like to amend the first array, or create a third, with just the links I actually want.
      Confused? Welcome to my world.
      I'll put the relevant section of code below to show where I am up to.
      I've left the _ArrayDisplay in the above code which I am using to check the code does what I wanted. So the above code works fine, I'm just stumped on how to extract the links from $aLinks that I want.
      $oLinks = _IELinkGetCollection($oIE) Local $aLinks[1] = ["Link"] For $oLink in $oLinks _ArrayAdd($aLinks, $oLink.href) Next ;_ArrayDisplay($aLinks) $aLinks = _ArrayUnique($aLinks) ;_ArrayDisplay($aLinks) $aLinksToClick = _ArrayFindAll($aLinks, "azref", 0, 0, 0, 3) ;_ArrayDisplay($aLinksToClick) Local $aSortedLinks[1] = ["Sorted Links"] For $i = 0 To UBound($aLinksToClick) - 1 $Blah = _ArrayExtract($aLinks, $aLinksToClick[$i], $aLinksToClick[$i]) _ArrayAdd($aSortedLinks, $Blah) Next