Trong Posted January 23 Posted January 23 I have to report hourly production and mental calculation or adding up the numbers seems to take a long time, so I'm trying to write a script to do the calculation quickly, but my logic is a bit bad, hope for help! Input data: (The exact data copied from the master management software table can be downloaded from here: https://www.liteon.vn/Big_Data.txt AND https://www.liteon.vn/Lite_Data.txt) N05 8587652 96908-003760A000 FG,GSUBUSW-Flex_NA,Packing FCC (USW 1-Nesscessary FATP FATP_PACKING_02 100 90 10 N Y N05 8587651 96908-003760A000 FG,GSUBUSW-Flex_NA,Packing US (USW 1-Nesscessary FATP FATP_PACKING_02 110 20 80 N Y N05 8587602 96908-003760A000 FG,GSUBUSW-Flex_NA,Packing UK (USW 1-Nesscessary FATP FATP_PACKING_02 105 10 95 N Y N05 8587252 96908-003760A000 FG,GSUBUSW-Flex_NA,Packing ASSY (UK 1-Nesscessary FATP FATP_PACKING_02 120 20 100 N Y N05 8587612 96908-003760A000 FG,GSUBUSW-Flex_NA,Packing FCC (USW 1-Nesscessary FATP FATP_FT_02 100 90 10 N Y N05 8587621 96908-003760A000 FG,GSUBUSW-Flex_NA,Packing US (USW 1-Nesscessary FATP FATP_FT_02 110 20 80 N Y N05 8587632 96908-003760A000 FG,GSUBUSW-Flex_NA,Packing UK (USW 1-Nesscessary FATP FATP_FT_02 105 10 95 N Y N05 8587242 96908-003760A000 FG,GSUBUSW-Flex_NA,Packing ASSY (UK 1-Nesscessary FATP FATP_FT_02 120 20 100 N Y N05 8587612 96908-003760A000 FG,GSUBUSW-Flex_NA,Packing FCC (USW 1-Nesscessary FATP FATP_FT_03 100 90 10 N Y N05 8587621 96908-003760A000 FG,GSUBUSW-Flex_NA,Packing US (USW 1-Nesscessary FATP FATP_FT_03 110 20 80 N Y N05 8587632 96908-003760A000 FG,GSUBUSW-Flex_NA,Packing UK (USW 1-Nesscessary FATP FATP_FT_03 105 10 95 N Y N05 8587242 96908-003760A000 FG,GSUBUSW-Flex_NA,Packing ASSY (UK 1-Nesscessary FATP FATP_FT_03 120 20 100 N Y N06 8584568 96801-002910A000 FG,WPUBUK-Ultra_NA,Packing FCC 1-Nesscessary FATP FATP_PRINT_01 100 10 90 N N N06 8584548 96801-002910A000 FG,WPUBUK-Ultra_NA,Packing US 1-Nesscessary FATP FATP_PRINT_01 101 10 91 N N N06 8584528 96801-002910A000 FG,WPUBUK-Ultra_NA,Packing ASSY (UK 1-Nesscessary FATP FATP_PRINT_01 110 10 100 N N N06 8586247 96801-003400A000 FG,WPUBU7-Pro_NA,Packing ASSY FCC (U7 1-Nesscessary FATP FATP_FT_01 102 12 90 N N N06 8586217 96801-003400A000 FG,WPUBU7-Pro_NA,Packing ASSY US (U7 1-Nesscessary FATP FATP_FT_01 104 14 90 N N N06 8586287 96801-003400A000 FG,WPUBU7-Pro_NA,Packing ASSY (UK 1-Nesscessary FATP FATP_FT_01 108 10 98 N N The table displays the desired output: PROCESS >>> |FATP_PACKING_02 | FATP_FT_01| FATP_FT_02 | FATP_FT_03| FATP_PRINT_01 MODEL ⇓ GSUBUSW-Flex | 140 | 0 | 140 | 140 | 0 WPUBUK-Ultra | 0 | 0 | 0 | 0 | 30 WPUBU7-Pro | 0 | 36 | 0 | 0 | 0 GSUBUSW-Flex FATP_PACKING_02 | 140 FATP_FT_02 | 140 FATP_FT_03 | 140 WPUBUK-Ultra FATP_PRINT_01 | 30 WPUBU7-Pro FATP_FT_01 | 36 I am stuck on how to save and retrieve data from array: expandcollapse popup#include <Array.au3> _GetYield() Func _GetYield() Global $aPROCESS_List = StringSplit('PC MAP-CASE|PC-AS-LQC|FATP-AS-GULE Filling1|FATP-AS-GULE Filling2|PC-BI-INPUT|PC-BI-OUTPUT|PC-PK-LOAD|PC-PK-HIPOT1|PC-PK-FIN-ATE1|PC-PK-PF-TEST1|PC-PK-UNLOAD|PC-PK-LQC2|PC-PK-LQC1|PC-PK-PACKING|PC-OQC|SMT_TOP_MAPPING_02|SMT_TOP_AOI|PWR-PC-HI-INPUT|PC_TU_ICT|PC_TU_INT|AI_INPUT|AI_OUTPUT|RI_INPUT|RI_OUTPUT|RI_VMI|SMT_TOP_INPUT|SMT_TOP_MOUNT|PC-PK-LASER|SMT_BOT_MAPPING_01|SMT_BOT_AOI|PC-USW-01|FATP-AS-USW-check1|FATP-AS-USW-Check2|PC-TU-INT-R|PK-AS-Thrust|PC-AS-LQC1|PC-PK-MAP-More material|SMT_BOT_INPUT|SMT_BOT_MOUNT|NB-KB-CHAS-Input|NB-KB-CHAS-AOI|NB-KB-CHAS-Packing|PC_Packing|PC_ASSY2|PC_Check|PC_QC|FATP_ASSY_06|Packing_OQC|DIP_INPUT|DIP_VMI_01|DIP_PACKING|DIP_OBA|FATP_INPUT|FATP_PCBA_01|FATP_PCBA_02|FATP_RUN IN|FATP_FT_01|FATP_PRINT_01|FATP_AOI_03|FATP_PRINT_02|FATP_AOI_04|FATP_AOI_05|FATP_PACKING_02|FATP_PCBA_03|FATP_LASER|FATP_AOI_01|FATP_FT_02|FATP_FT_03|FATP_ASSY_01|FATP_HIPOT_TEST|FATP_BURN_IN|FATP_FT_04|FATP_FT_05|FATP_MAPPING_01|FATP_Leak Test|FATP_AOI_02|FATP_FCD TEST|FATP_ASSY_LCM|FATP_AOI_06|FATP_VMI_01|PC_Packing_SP|PC_CCD|PC_Check_SP|Tampo|KM_ASM_Cosmetic|KM_TES_ATE|KM_ASM_Mapping|KM_ASM_Box|KM_PAC_Carton|PC_OQC|KM_PAC_Pallet|KM_TES_AOI|KM_TES_Pair test|KM_FATP_KB_Input|KM_TES_RF test|KM_Semi_Output|QC-FT01|FATP_FT_06|QC-FT02|FATP_ASSY_02|KM_ASM_SN|FATP_OQC|KM_ASM_Input|KM_TES_bluetooth|KM_TES_Backlight|FATP-LASER_02|QC_IR_TEST|QC-FT04|FATP_ASSY_04|FATP_AOI_07|FATP_AOI_08|FATP_ASSY_03|FATP_CHECK|FATP_PRINT_03|FATP_GLUE_IN|FATP_GLUE_OUT|FATP_TEST_PULL|FATP_Leak Test02|FATP_PCBA_04|PL_PACKING|SMT_BOT_PRINT|DIP_TU|DIP_PCBA_01|DIP_PCBA Test02|DIP_Funtion Test|DIP_OQC|DIP_INSERT_01|DIP_PCBA Test03|DIP_WAVESOLDER|DIP_RF Test|DIP_Assy 01|DIP_ICT_01|DIP_ICT_02|SMT Laser|CIPS-TU-FUNCTION|DIP_FCD_01|PWR-PC-HI-LQC|RI_Mapping|DIP_AOI_01|COB_INPUT|COB_Test_01|COB_Test_02|COB_VMI|SMT_XRAY_BOT|DIP_ Tes Wheel|SMT_TOP_VMI|DIP_Test ADD FW|DIP_Test Adaptor 1|DIP_Test Adaptor 2|DIP_Keo|DIP_TEST FW|CIPS-HISN-MAP|CIPS-TU-LQC1-MAP|CIPS-TU-LeadLength|CIPS-TU-AOI-Check|CIPS-TU-ICT|CIPS-TU-LQC2|CIPS-AS-LQC1|AS-SSN-MAP|CIPS-AS-FRU-W|CIPS-AS-IAC|CIPS-AS-PRE-ATE|CIPS-BI-Input|CIPS-BI-Output|CIPS-PK-PRINT|CIPS-PK-Hipot|CIPS-PK-FINAL-ATE|CIPS-PK-FRU-V|CIPS-PK-Measure|CIPS-PK-LQC1|CIPS-PK-LQC2|CIPS-Packing|SMT_Test_ICT|FATP_PACKING_05|PS_QC|PS_Machine|PS_Packing|PL_OBA|PL_Machine_4201|PL_Machine_4202|SMT_OBA|SMT_TOP_SPI|SMT_TOP_AOI_BR|SMT_TOP_MOUNT_02|SMT_PACKING|NB-KB-FINAL-Pull Key|NB-KB-FINAL-LED Test|NB-KB-FINAL-Autofeeling|NB-KB-FINAL-BL Test|NB-KB-FINAL-FQC|NB-KB-FINAL-AOI|NB-KB-FINAL-Packing|NB-KB-FINAL-OBA|NB-KB-FINAL-Input|NB-KB-FINAL-VI|KM_ASM_Flatness|KM_ASM_AOI|PC-PK-SCP-OVP|PC-PK-FIN-ATE2|PC-AS-ICBURN|PC-AS-PT|PC-PK-MAP-Cable|CIPS-PK-ACC-V|CIPS-OQC-Hipot|CIPS-OQC-ATE|CIPS-OQC-FRU-V|PC_TU_LQC1|PC-OQC-HIPOT|PC-OQC-ATE|PC-PK-FAN LED|PC-PK-SDQ|PC-PK-LQC3|PC-PK-LQC4|PC MAP-CASE-CCD|PC-AS-Leak test|PC-TU-AOI|PC-AS-LQC2|PC-PK-ID-SSN-MAP|PC-PK-ID-SSN-MAP-READ', '|') Global $aMODEL_List Local $CSV_Yield = ClipGet() If Not StringInStr($CSV_Yield, @TAB) Then $CSV_Yield = FileRead(@ScriptDir & "\Lite_Data.txt") ;MsgBox(48 + 262144, 'Yield Report', 'Please COPY ' & @CRLF & 'PDLINE_NAME' & @CRLF & 'WORK_ORDER' & @CRLF & 'PART_NO' & @CRLF & 'MODEL_NAME' & @CRLF & 'PROCESS_TYPE' & @CRLF & 'STAGE_NAME' & @CRLF & 'PROCESS_NAME' & @CRLF & 'TARGET_QTY' & @CRLF & 'OUTPUT_QTY' & @CRLF & 'WAIT_INPUT' & @CRLF & 'STAGE_INPUT' & @CRLF & 'STAGE_OUTPUT' & @CRLF &'xong thi BAM OK', 60) ;$CSV_Yield = ClipGet() EndIf If Not StringInStr($CSV_Yield, @TAB) Then Exit $CSV_Yield = StringReplace($CSV_Yield, @CRLF, @LF) $CSV_Yield = StringReplace($CSV_Yield, @TAB, '|') While StringInStr($CSV_Yield, ' ' & ' ') $CSV_Yield = StringReplace($CSV_Yield, ' ' & ' ', ' ') WEnd Local $NewContent, $iNewContent, $xNewContent, $PROCESS_List, $MODEL_List Local $aLine, $iLine, $aArray_Yield = StringSplit($CSV_Yield, @LF) If IsArray($aArray_Yield) Then Local $iModelName, $iProcessName, $iOutput Local $a_PROCESS_List[1], $a_MODEL_List[1] For $i = 1 To $aArray_Yield[0] $aLine = StringSplit($aArray_Yield[$i], '|', 3) If IsArray($aLine) Then If UBound($aLine) > 8 Then _Get_Info($iModelName, $iProcessName, $iOutput, $aArray_Yield[$i]) If (StringLen($iModelName) > 1) And (StringLen($iProcessName) > 1) And ($iOutput > 0) Then If Not StringInStr($MODEL_List, $iModelName) Then $MODEL_List&=$iModelName&'|' If Not StringInStr($PROCESS_List, $iProcessName) Then $PROCESS_List&=$iModelName&'|' _ArrayAdd($a_MODEL_List, $iModelName) _ArrayAdd($a_PROCESS_List, $iProcessName) EndIf EndIf EndIf $iModelName = '' $iProcessName = '' $iOutput = 0 $aLine = 0 Next _ArrayUnique($a_MODEL_List) _ArrayUnique($a_PROCESS_List) ;~ For $i = 1 To $aArray_Yield[0] ;~ ;ConsoleWrite("$a [" & $i & "] _ " & $aArray_Yield[$i] & @CRLF) ;~ $aLine = StringSplit($aArray_Yield[$i], '|', 3) ;~ If IsArray($aLine) Then ;~ If UBound($aLine) > 8 Then ;~ ;ConsoleWrite('-$aLine: '&UBound($aLine)&@CRLF) ;~ _Get_Info($iModelName, $iProcessName, $iOutput, $aArray_Yield[$i]) ;~ If (StringLen($iModelName) > 1) And (StringLen($iProcessName) > 1) And ($iOutput > 0) Then ;~ ;ConsoleWrite('-Model: ' & $iModelName & ' -Process: ' & $iProcessName & ' -Output: ' & $iOutput & @CRLF) ;~ ; Array: MODEL | PROCESS | OUTPUT ;~ ;~ ; | PROCESS_1 | PROCESS_2 | PROCESS_3 | PROCESS_N ;~ ;MODEL_1 | OUTPUT_X | OUTPUT_X | OUTPUT_X | OUTPUT_X ;~ ;MODEL_2 | OUTPUT_X | OUTPUT_X | OUTPUT_X | OUTPUT_X ;~ ;MODEL_3 | OUTPUT_X | OUTPUT_X | OUTPUT_X | OUTPUT_X ;~ ;MODEL_N | OUTPUT_X | OUTPUT_X | OUTPUT_X | OUTPUT_X ;~ EndIf ;~ EndIf ;~ EndIf ;~ $iModelName = '' ;~ $iProcessName = '' ;~ $iOutput = 0 ;~ $aLine = 0 ;~ Next EndIf ;~ ConsoleWrite($MODEL_List&@CRLF) ;~ ConsoleWrite($PROCESS_List&@CRLF) ;~ ConsoleWrite($xNewContent&@CRLF) ;~ ConsoleWrite($NewContent&@CRLF) EndFunc ;==>_TinhYield Func _Get_Info(ByRef $iModelName, ByRef $iProcessName, ByRef $iOutput, $iLine = '|') Local $aLine = StringSplit($iLine, '|', 3) If IsArray($aLine) Then If (UBound($aLine) > 8) Then $iLine = _Get_ModelName($aLine[4]) If (StringLen($iLine) > 1) Then ;ConsoleWrite('-$aLine: '&UBound($aLine)&@CRLF) $iModelName = $iLine $iProcessName = $aLine[7] If Int($aLine[9]) > 0 Then $iOutput = $aLine[9] Else $iOutput = 0 EndIf ;ConsoleWrite('-Model: '&$aLine[4]&' -Process: '&$aLine[7]&' -Output: '&$aLine[9]&@CRLF) EndIf EndIf EndIf EndFunc ;==>_Get_Info Func _Get_ModelName($iLine = ',,,,', $iRetunNoCountry = True) Local $aLine = StringSplit($iLine & ',,,,', ',', 3) If IsArray($aLine) Then StringReplace($iLine, ',', '') If (@extended < 2) Then If (StringLen($aLine[0]) > 1) And (StringLen($aLine[1]) > 1) Then ;ConsoleWrite('-0: ' & $aLine[0] & ' -1: ' & $aLine[1] & ' -2: ' & $aLine[2] & ' -3: ' & $aLine[3] & @CRLF) $iLine = StringReplace($aLine[0] & '_' & $aLine[1], ' ', '-') Return $iLine EndIf Else If (StringLen($aLine[1]) > 1) Then ;ConsoleWrite('-0: ' & $aLine[0] & ' -1: ' & $aLine[1] & ' -2: ' & $aLine[2] & ' -3: ' & $aLine[3] & @CRLF) $iLine = StringReplace($aLine[1], '_NA', '') If (StringStripWS($iLine, 8) <> '') Then If $iRetunNoCountry Then Return $iLine If StringInStr($aLine[2], 'Packing ASSY FCC') Then Return $iLine & '_FCC' ElseIf StringInStr($aLine[2], 'Packing ASSY US') Then Return $iLine & '_US' ElseIf StringInStr($aLine[2], 'Packing ASSY WW') Then Return $iLine & '_WW' ElseIf StringInStr($aLine[2], 'Packing ASSY EU') Then Return $iLine & '_EU' ElseIf StringInStr($aLine[2], 'Packing ASSY UK') Then Return $iLine & '_UK' Else Return $iLine EndIf EndIf Return '' EndIf EndIf EndIf EndFunc ;==>_Get_ModelName Thanks a lot Regards,
AspirinJunkie Posted January 23 Posted January 23 (edited) As I see it, you have to group and filter data, create totals for the groups, etc. This literally cries out for a job for SQLite. Once you have the data in an SQLite database, the groupings and totals are a piece of cake. To do this, however, you would first have to define a basic structure for this table in the database. In other words, which columns have which names and which data type. The import is then best done in milliseconds via _SQLite_SQLiteExe() in conjunction with the .import command and tabulator as column separator. Otherwise, it is not yet clear to me personally how you get from the input data to the table that you designate as desired output. The model is hidden somewhere as a substring in the 4th column. The rules for extracting it are not clear. And I also don't understand how these values of 140 are created from the initial values. No sum, average or anything else that you could derive from your example extract results in your desired values. Of course, you can also handle the import, filtering and grouping with pure AutoIt. But then I would recommend that you use ready-made UDFs to help you. Here is an example of how you could proceed with your data using the TableData UDF, for example: #include "TableData.au3" ; Transfer data to table type - customize attribute names in the header to your needs $mData = _td_fromCsv("Big_Data.txt", @TAB, 0, "Attrib1|Attrib2|Attrib3|Model|Attrib5|Attrib6|Process|Attrib8|Attrib9|Attrib10|Attrib11|Attrib12") ; display data _td_display($mData, "Big_Data - whole dataset") ; group by model and process - the example here attempts to extract the model from the model column and concat it with the process to form a group string $mGroups = _td_groupBy($mData, "__extractModel($x.Model) & ' | ' & $x.Process") ; Go through the individual groups and process them. Here you could do your sums or whatever For $sKey In MapKeys($mGroups) ; the current group data as table object $aGroupData = $mGroups[$sKey] ; calculate the sum over the 8th attribute for the current group $fSum = _td_reduce($aGroupData, "+ $x.Attrib8", 0) ConsoleWrite(StringFormat("% 70s: %6d\n", $sKey, $fSum)) ; display the current group data ;~ _td_display($aGroupData, $sKey) Next ; auxiliary function to extract the model name from the total string in the 4th column. You will certainly have to adapt this to your needs Func __extractModel($sString) Local $aSplit = StringSplit($sString, ",", 3) If UBound($aSplit) < 2 Then Return $sString Return $aSplit[1] EndFunc Edited January 23 by AspirinJunkie Trong 1
Trong Posted January 24 Author Posted January 24 Thanks, I will research further 19 hours ago, AspirinJunkie said: Otherwise, it is not yet clear to me personally how you get from the input data to the table that you designate as desired output. The model is hidden somewhere as a substring in the 4th column. The rules for extracting it are not clear. And I also don't understand how these values of 140 are created from the initial values. No sum, average or anything else that you could derive from your example extract results in your desired values. 140 is the sum of the "all products that have been produce" quantity of "all orders" , Each order is 1 line. Column 1 is the total quantity of 1 order Column 2 is the number of products that have been produced Column 3 is the number of remaining unproduced products. Regards,
AspirinJunkie Posted January 24 Posted January 24 Ok, with the information I have refined the script a little so that it may come closer to your requirements. You can certainly do the fine-tuning yourself. #include "TableData.au3" ; Transfer data to table type - customize attribute names in the header to your needs $mData = _td_fromCsv("Big_Data.txt", @TAB, 0, "Attrib1|Attrib2|Attrib3|Model|Attrib5|Attrib6|Process|Quantity|Produced|Unproduced|Attrib11|Attrib12") ; filter the data so that only elements whose process begins with "FATP_" are retained $mData = _td_filter($mData, 'StringRegExp($x.Process, "^FATP_.*")') ; [optional] sort by model and process for a better overview _td_sort($mData, "__extractModel($x.Model) & $x.Process ") ; display data _td_display($mData, "Big_Data - filtered dataset") ;group by model and process - the example here attempts to extract the model from the model column and concat it with the process to form a group string For $aGroupData In _td_groupBy($mData, "__extractModel($x.Model) & ' | ' & $x.Process") ; Go through the individual groups and process them. Here you could do your sums or whatever ; convert the datasets to key-value maps where the keys are the attributes for better handling (access the values by attribute name instead of index number) $aGroup = _td_toObjects($aGroupData) ; calculate the sum over the "Produced" attribute for the current group $fSum = _td_reduce($aGroupData, "+ $x.Produced", 0) ; print the model, the process and the sum of produced units ConsoleWrite(StringFormat("%-27s %-18s: %5d\n", __extractModel($aGroup[0].Model), $aGroup[0].Process, $fSum)) Next ; auxiliary function to extract the model name from the total string in the 4th column. You will certainly have to adapt this to your needs Func __extractModel($sString) Local $aSplit = StringSplit($sString, ",", 3) If UBound($aSplit) < 2 Then Return $sString Return StringRegExpReplace($aSplit[1], '\h*_.+$', '') EndFunc Trong 1
Solution pixelsearch Posted January 24 Solution Posted January 24 @Trong Hi Here is a quick attempt using only AutoIt, it seems to work fine. I have a concern when "Big_Data.txt" is used, because 222 columns are generated (222 unique process names) Also I don't know if the columns names (the process names) need to be ordered in a specific way, in which case some code should be added, you'll see. I'm using in this script a great function from @AspirinJunkie initially named _StringSplit2D, I tweaked it a bit to add a couple of parameters and make it even more flexible, it's very useful. Your function _Get_ModelName() is in the script, untouched, to extract the model name and place it in a new column #13 ... while _StringSplit2D is performing, best moment to do this ! expandcollapse popup#include <Array.au3> #include <MsgBoxConstants.au3> Opt("MustDeclareVars", 1) ;0=no, 1=require pre-declaration _GetYield() ;============================================== Func _GetYield() Local $sFileName = @ScriptDir & "\OP example.txt" ; Local $sFileName = @ScriptDir & "\Lite_Data.txt" ; Local $sFileName = @ScriptDir & "\Big_Data.txt" If Not FileExists($sFileName) Then Exit MsgBox($MB_TOPMOST, "Error", _ $sFileName & @crlf & @crlf & "File doesn't exist") Local $sFileRead = FileRead($sFileName) If @error Then Exit MsgBox($MB_TOPMOST, "FileRead : error " & @error, "") Local $aArray = _StringSplit2D_EX($sFileRead, @TAB, @CRLF, False, 1) ; last param. 1 => add 1 column on the right (column 13) If @error Then Exit MsgBox($MB_TOPMOST, "_StringSplit2D_EX : error " & @error, _ "All rows don't have the same number of columns" & @crlf & @crlf & _ "line 0 versus line " & @extended) _ArrayDisplay($aArray, "Initial data + column 13 added on the right") Local $aModel = _ArrayUnique($aArray, 13, 0, 0, $ARRAYUNIQUE_NOCOUNT) _ArrayDisplay($aModel, "Unique Models") Local $aProcess = _ArrayUnique($aArray, 7, 0, 0, $ARRAYUNIQUE_NOCOUNT) _ArrayDisplay($aProcess, "Unique Process") Local $aResult[Ubound($aModel)][Ubound($aProcess) + 1], $mModel[], $mProcess[], $sHeader, $iRow, $iCol ; Final array : fill the model column with unique model names For $i = 0 To Ubound($aModel) - 1 $aResult[$i][0] = $aModel[$i] $mModel[$aModel[$i]] = $i Next ; Final array : prepare the headers with unique process names For $j = 0 To Ubound($aProcess) - 1 $sHeader &= $aProcess[$j] & "|" $mProcess[$aProcess[$j]] = $j Next ; Final array : update any cell with its new value For $i = 0 To Ubound($aArray) - 1 $iRow = $mModel[$aArray[$i][13]] $iCol = $mProcess[$aArray[$i][7]] $aResult[$iRow][$iCol + 1] += $aArray[$i][9] Next _ArrayDisplay($aResult, "Result", Default, $ARRAYDISPLAY_NOROW, Default, "Models|" & $sHeader) EndFunc ;==>_GetYield ;============================================== Func _StringSplit2D_EX(ByRef $sString, $sDelim_Col = "|", $sDelim_Row = @CRLF, $bExpand = False, $iAdd_EmptyCol = 0) ; based on AspirinJunkie's function _StringSplit2D found at https://autoit.de/thread/85380-1d-array-in-2d-array-splitten/ ; Thanks Nine for suggesting the 4th parameter $bExpand, to allow or not the same number of fields per row (as in _FileReadToArray) Local $a_FirstDim = StringSplit($sString, $sDelim_Row, $STR_ENTIRESPLIT + $STR_NOCOUNT) Local $iKeep_NbCol = Ubound(StringSplit($a_FirstDim[0], $sDelim_Col, $STR_ENTIRESPLIT + $STR_NOCOUNT)) ; keep nb cols row 0 Local $a_Out[UBound($a_FirstDim)][1 + $iAdd_EmptyCol], $a_Line, $i_2DMax = 1 For $i = 0 To UBound($a_FirstDim) - 1 $a_Line = StringSplit($a_FirstDim[$i], $sDelim_Col, $STR_ENTIRESPLIT + $STR_NOCOUNT) If (Not $bExpand) And (Ubound($a_Line) <> $iKeep_NbCol) Then Return SetError(3, $i, 0) ; same error # as _FileReadToArray If UBound($a_Line) > $i_2DMax Then ; when $bExpand = False, this test will be True maximum 1 time, never more. $i_2DMax = UBound($a_Line) ReDim $a_Out[UBound($a_Out)][$i_2DMax + $iAdd_EmptyCol] EndIf For $j = 0 To UBound($a_Line) - 1 $a_Out[$i][$j] = $a_Line[$j] Next $a_Out[$i][$j] = _Get_ModelName($a_Line[4]) ; model name in last column added on the right Next Return $a_Out EndFunc ;==>_StringSplit2D_EX ;============================================== Func _Get_ModelName($iLine = ',,,,', $iRetunNoCountry = True) Local $aLine = StringSplit($iLine & ',,,,', ',', 3) If IsArray($aLine) Then StringReplace($iLine, ',', '') If (@extended < 2) Then If (StringLen($aLine[0]) > 1) And (StringLen($aLine[1]) > 1) Then ;ConsoleWrite('-0: ' & $aLine[0] & ' -1: ' & $aLine[1] & ' -2: ' & $aLine[2] & ' -3: ' & $aLine[3] & @CRLF) $iLine = StringReplace($aLine[0] & '_' & $aLine[1], ' ', '-') Return $iLine EndIf Else If (StringLen($aLine[1]) > 1) Then ;ConsoleWrite('-0: ' & $aLine[0] & ' -1: ' & $aLine[1] & ' -2: ' & $aLine[2] & ' -3: ' & $aLine[3] & @CRLF) $iLine = StringReplace($aLine[1], '_NA', '') If (StringStripWS($iLine, 8) <> '') Then If $iRetunNoCountry Then Return $iLine If StringInStr($aLine[2], 'Packing ASSY FCC') Then Return $iLine & '_FCC' ElseIf StringInStr($aLine[2], 'Packing ASSY US') Then Return $iLine & '_US' ElseIf StringInStr($aLine[2], 'Packing ASSY WW') Then Return $iLine & '_WW' ElseIf StringInStr($aLine[2], 'Packing ASSY EU') Then Return $iLine & '_EU' ElseIf StringInStr($aLine[2], 'Packing ASSY UK') Then Return $iLine & '_UK' Else Return $iLine EndIf EndIf Return '' EndIf EndIf EndIf EndFunc ;==>_Get_ModelName Apart from the "Lite_Data.txt" and the "Big_Data.txt" you provided in your 1st post, I attach below your example (with Tab separators) in a file named "OP example.txt", the famous example where results equal 140 OP example.txt Trong 1 "I think you are searching a bug where there is no bug..."
Trong Posted January 25 Author Posted January 25 Thank you AspirinJunkie and pixelsearch for helping make my work easier. Regards,
ioa747 Posted January 25 Posted January 25 approach as function for reusing #include <Array.au3> #include <File.au3> Global $salesData = [ _ ["Product", "Region", "Sales"], _ ["Laptop", "North", 150], _ ["Smartphone", "South", 200], _ ["Laptop", "South", 100], _ ["Tablet", "North", 75], _ ["Smartphone", "North", 180], _ ["Desktop", "South", 300], _ ["Laptop", "North", 250], _ ["Smartphone", "North", 120]] Global $aSumTbl = _ArrayCrosstab($salesData, 0, 1, 2, 1) _ArrayDisplay($aSumTbl, "$aSumTbl") _ArrayCrosstab expandcollapse popup; https://www.autoitscript.com/forum/topic/212654-help-with-data-processing-and-reporting/#findComment-1540469 #include <Array.au3> #include <File.au3> Opt("MustDeclareVars", 1) ;0=no, 1=require pre-declaration Global $aTbl = _GetAsTable(@ScriptDir & "\OP example.txt", 0) ; "\Lite_Data.txt" If Not IsArray($aTbl) Then Exit MsgBox(16, "Error", "Failed to load the table from the file.") _ArrayDisplay($aTbl, "$aTbl") Global $aSumTbl = _ArrayCrosstab($aTbl, 0, 7, 9) _ArrayDisplay($aSumTbl, "$aSumTbl") ; #FUNCTION# -------------------------------------------------------------------------------------------------------------------- ; Name...........: _ArrayCrosstab ; Description....: Summarize an array by grouping and counting values. ; Syntax.........: _ArrayCrosstab(Const ByRef $aArray, $iRowHeading, $iColumnHeading, $iValue, $iBase = 0, $iValueType = 1) ; Parameters.....: $aArray - The array to summarize. ; $iRowHeading - The column number for the Row Heading value in the cross-tabulation. ; $iColumnHeading - The column number for the Column Heading value in the cross-tabulation. ; $iValue - The column number of the value to sum or count or max or min, in the cross-tabulation. ; $iBase - [Optional] The array is 0-based or 1-based. Default 0 = 0-based. ; $iValueType - [Optional] The type of value to summarize. Default is 1 = sum. ; 1 = sum - Default ; 2 = cnt ; 3 = max ; 4 = min (excluding null or zero) ; Return values..: A two-dimensional array containing the aggregated data. ; Author ........: ioa747 ; Notes .........: This function uses the _ArrayUnique() function to get unique values for $iRowHeading, $iColumnHeading ; Link ..........: ; Dependencies...: #include <Array.au3>, #include <File.au3> ;-------------------------------------------------------------------------------------------------------------------------------- Func _ArrayCrosstab(Const ByRef $aArray, $iRowHeading, $iColumnHeading, $iValue, $iBase = 0, $iValueType = 1) If $iValueType < 1 Or $iValueType > 4 Then $iValueType = 1 If $iBase < 0 Or $iBase > 1 Then $iBase = 0 If Not IsArray($aArray) Then Return SetError(1, 0, "Is Not Array") Local $mRow[], $mCol[] ; Get the Row Heading values Local $aRow = _ArrayUnique($aArray, $iRowHeading, $iBase) For $i = 1 To $aRow[0] $mRow[$aRow[$i]] = $i Next ; Get the Column Heading values Local $aCol = _ArrayUnique($aArray, $iColumnHeading, $iBase) For $i = 1 To $aCol[0] $mCol[$aCol[$i]] = $i Next ; initialize the Result array Local $aResult[UBound($aRow)][UBound($aCol)] ; add Column Heading to Result array For $i = 1 To $aCol[0] $aResult[0][$i] = $aCol[$i] Next ; add Row Heading to Result array For $i = 1 To $aRow[0] $aResult[$i][0] = $aRow[$i] Next $aResult[0][0] = $aRow[0] ; fill the Values to Result array Switch $iValueType Case 1 ; sum For $i = $iBase To UBound($aArray) - 1 $aResult[$mRow[$aArray[$i][$iRowHeading]]][$mCol[$aArray[$i][$iColumnHeading]]] += $aArray[$i][$iValue] Next Case 2 ; cnt For $i = $iBase To UBound($aArray) - 1 $aResult[$mRow[$aArray[$i][$iRowHeading]]][$mCol[$aArray[$i][$iColumnHeading]]] += 1 Next Case 3 ; max For $i = $iBase To UBound($aArray) - 1 Local $currentValue = $aArray[$i][$iValue] If $currentValue > $aResult[$mRow[$aArray[$i][$iRowHeading]]][$mCol[$aArray[$i][$iColumnHeading]]] Then $aResult[$mRow[$aArray[$i][$iRowHeading]]][$mCol[$aArray[$i][$iColumnHeading]]] = $currentValue EndIf Next Case 4 ; min (excluding null or zero) For $i = $iBase To UBound($aArray) - 1 Local $currentValue = $aArray[$i][$iValue] If $currentValue <> 0 And $currentValue <> "" Then If $aResult[$mRow[$aArray[$i][$iRowHeading]]][$mCol[$aArray[$i][$iColumnHeading]]] = 0 Or _ $currentValue < $aResult[$mRow[$aArray[$i][$iRowHeading]]][$mCol[$aArray[$i][$iColumnHeading]]] Then $aResult[$mRow[$aArray[$i][$iRowHeading]]][$mCol[$aArray[$i][$iColumnHeading]]] = $currentValue EndIf EndIf Next EndSwitch Return $aResult EndFunc ;==>_ArrayCrosstab ;--------------------------------------------------------------------------------------- Func _GetAsTable($sFileName, $iBase = 1, $sDelimiter = @TAB) Local $vReturn ; Read the file into an array _FileReadToArray($sFileName, $vReturn, $iBase, $sDelimiter) ;_ArrayDisplay($vReturn) ; Extract the model name from the 4th column and put it in the 1st column (it was empty) For $i = 0 To UBound($vReturn) - 1 Local $aModel = StringSplit($vReturn[$i][4], "_") ; model data $vReturn[$i][0] = ($aModel[0] > 0 ? StringTrimLeft($aModel[1], 3) : $aModel[1]) Next Return $vReturn EndFunc ;==>_GetAsTable Happy coding! Trong 1 I know that I know nothing
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now