Search the Community

Hey Guys! I was hopping I could get some help on this topic, I will try to explain what i am trying to do as clearly as I can! In a nutshell, I am using the API a program and attempting to find all of the "TRACE FIELD" objects within its active page. using the code you see below i can do just that, upon executing the code the For loop will run over and over, going through each trace field and grabbing the text in each one until there are none left. My question is how could I take the text from each instance and save it into either individual variables or one large variable that houses all the data before the look resets and wipes the text from the previous iteration? I hope this made sense, thanks in advanced guys! Kruxe

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