1 2 Previous Next 24 Replies Latest reply on Aug 30, 2015 12:51 PM by okramis

    How to sum tab-separated values?

    JavierDura

      Hi.

       

      I have a text field with several numbers separated by tabs. Something like this:

       

      1     3     4     5

      2     5     6     8

      1     2     3     5

       

      and I want to get the sum of each column separated by tabs aswell:

       

      4     10     13     18

       

      Considering that:

      - I don´t know in advanced the number of columns or the number of lines in the field

      - every line will have the same number of values (4 values in the example)

       

      ...is it possible to get the result I need using a calculation (not an script)?

       

      Thank you.

        • 1. Re: How to sum tab-separated values?
          PeterWindle

          I have not done this, but one would imagine, you would need to build a recursive custom function that would "loop" through all values (using the 'tab' character as the separating identifier) I'd be keen to give this a bash but I'm just about to have dinner, I will try something tonight if I have time.

           

          PS: I'm only learning recursive custom functions now, so I'm thinking I'll have a go later anyway.

          • 2. Re: How to sum tab-separated values?
            wimdecorte

            Why not a script?

            • 3. Re: How to sum tab-separated values?
              okramis

              I have a text field with several numbers separated by tabs. Something like this:

               

              1     3     4     5

              2     5     6     8

              1     2     3     5

               

              and I want to get the sum of each column separated by tabs aswell:

               

              4     10     13     18

               

               

              Feed a Virtual List with the content of the text field and do a ExecuteSQL() on it:

               

              Assuming your VL gets fed by $$array, the field delimiter of the VL is tab and the field names of the VL are col_01 to col col_04 and their data type is text, do the following:

               

              Let ( [

              $$array = yourTextField

              ] ;

              ExecuteSQL ( "

              SELECT SUM(NUMVAL(col_01)), SUM(NUMVAL(col_02)), SUM(NUMVAL(col_03)), SUM(NUMVAL(col_04))

              FROM VirtualList

              WHERE col_01 || col_02 || col_03 || col_04 IS NOT NULL

              " ; char(9) ; "" )

              )

              • 4. Re: How to sum tab-separated values?
                JavierDura

                Hi, Wim.

                It´s a performance issue.

                I´ve done this with a script but it´s time consuming.

                I want to try with a recursive custom function to see if it´s more efficient.

                • 5. Re: How to sum tab-separated values?
                  JavierDura

                  Okramis,

                   

                  Thank you for your answer.

                  Remember that I don´t know in advanced the number of columns or the number of lines in the field.

                  • 6. Re: How to sum tab-separated values?
                    okramis

                    you only have to make sure, that the number of records and number of columns in the VL are >= max expected col/rows then put all possible columns into the query or build it dynamically by checking the number of columns first i.e.

                     

                    ValueCount ( Substitute ( LeftValue ( yourTextField ) ; Char(9) ; "¶" ) )

                    • 7. Re: How to sum tab-separated values?
                      erolst

                      Try

                       

                      // SumColumns ( input )

                      Case (

                      IsEmpty ( $curRow ) or $colCounter = ValueCount ( $curRow ) ;

                        Let ( [

                          $rowCounter = $rowCounter + 1 ;

                          $curRow = Substitute ( GetValue ( input ; $rowCounter ) ; Char(9) ; ¶ ) ;

                          $colCounter = ""

                          ] ;

                          ""

                        ) ;

                        Let ( [

                          $colCounter = $colCounter + 1 ;

                          curVal = GetValue ( $curRow ; $colCounter ) ;

                          $res =

                            List (

                              Let ( ~l = LeftValues ( $res ; $colCounter - 1 ) ; Left ( ~l ; Length ( ~l ) - 1 ) ) ;

                              GetValue ( $res ; $colCounter ) + curVal ;

                              RightValues ( $res ; ValueCount ( $res ) - $colCounter )

                            )

                          ] ; 

                         "" 

                        )

                      )

                      &

                      Case (

                        $rowCounter > ValueCount ( input ) ;

                        Let ( [

                          res = Substitute ( $res ; Char(13) ; Char(9) ) ;

                          $res = "" ;

                          $curRow = "" ;

                          $rowCounter = ""

                          ] ;

                          res

                         ) ;

                        SumColumns ( input )

                      )

                      • 8. Re: How to sum tab-separated values?
                        electon

                        Looks Indeed as a job for a recursive custom function.

                        So far I could only come up with a solution with two custom functions.

                        They are very similar so possibly could be combined into one.

                         

                        The first one sums two rows of columns, the second one does the summing for all the rows.

                        They are both in the example file.

                         

                        No error checking is implemented, so assuming the array is sound it will work.

                        If someone spots a problem or maybe how to write it more efficiently, feel free to correct.

                        • 9. Re: How to sum tab-separated values?
                          okramis

                          Attached sample takes max 20 columns and max 500 rows. To add more rows just generate more records in Tem_01, to add more columns duplicate col_20 and rename it to col_21..., add the additional columns to the query in ∑NumberList.

                          • 10. Re: How to sum tab-separated values?
                            Menno

                            This is not as neat as the function from electron, but it's only 1 function. It also has no error checking. This particular function assumes that there are a maximum number of columns (in this case only 10) but that can be extended .... less columns is no problem  .... if not full arrays are no problem nor are "gaps"

                             

                            SumMatrix ( matrix ; result )

                             

                            Let ( [

                            /* Count the remaining lines in the array */

                              X = ValueCount ( matrix ) ;

                             

                            /* Get the first array for the calculation of newresult, fill "gaps" with zero's so the correct columns are added */

                              array = Substitute ( GetValue ( matrix ; 1 ) ; [ Char ( 9 ) & Char ( 9 ) ; Char ( 9 ) & "0" & Char ( 9 ) ] ) ;

                             

                            /* Count the columns */

                              columns = WordCount ( array ) ;

                             

                            /* Get remainder of the matrix */

                              newmatrix = MiddleValues ( matrix ; 2 ; X - 1 ) ;

                             

                            /* Add individual values of the array to the individual values of the result */

                            newresult = LeftWords (

                            Substitute (

                            List (

                            /* Extend this list if more then 10 columns are needed, to have less columns is no problem */

                            GetAsNumber ( MiddleWords ( array ; 1 ; 1 ) ) + GetAsNumber ( MiddleWords ( result ; 1 ; 1 ) ) ;

                            GetAsNumber ( MiddleWords ( array ; 2 ; 1 ) ) + GetAsNumber ( MiddleWords ( result ; 2 ; 1 ) ) ;

                            GetAsNumber ( MiddleWords ( array ; 3 ; 1 ) ) + GetAsNumber ( MiddleWords ( result ; 3 ; 1 ) ) ;

                            GetAsNumber ( MiddleWords ( array ; 4 ; 1 ) ) + GetAsNumber ( MiddleWords ( result ; 4 ; 1 ) ) ;

                            GetAsNumber ( MiddleWords ( array ; 5 ; 1 ) ) + GetAsNumber ( MiddleWords ( result ; 5 ; 1 ) ) ;

                            GetAsNumber ( MiddleWords ( array ; 6 ; 1 ) ) + GetAsNumber ( MiddleWords ( result ; 6 ; 1 ) ) ;

                            GetAsNumber ( MiddleWords ( array ; 7 ; 1 ) ) + GetAsNumber ( MiddleWords ( result ; 7 ; 1 ) ) ;

                            GetAsNumber ( MiddleWords ( array ; 8 ; 1 ) ) + GetAsNumber ( MiddleWords ( result ; 8 ; 1 ) ) ;

                            GetAsNumber ( MiddleWords ( array ; 9 ; 1 ) ) + GetAsNumber ( MiddleWords ( result ; 9 ; 1 ) ) ;

                            GetAsNumber ( MiddleWords ( array ; 10 ; 1 ) ) + GetAsNumber ( MiddleWords ( result ; 10 ; 1 ) ) ) ;

                            [ ¶ ; Char ( 9 ) ] ) ;

                            columns )

                            ] ;

                              /* Add the next array until no arrays are left */

                              If ( X > 0 ; SumMatrix ( newmatrix ; newresult ) ; result )

                            )

                            • 11. Re: How to sum tab-separated values?
                              BruceRobertson

                              Why do you have a text field filled with data like this instead of properly structured data in child records?

                              • 12. Re: How to sum tab-separated values?
                                JavierDura

                                Bruce,

                                 

                                This data comes from something like a price list that the user copy & paste in a field. It´s the way I receive the data, so I cannot control anything else.

                                • 13. Re: How to sum tab-separated values?
                                  JavierDura

                                  Good job, Electon.

                                  Thank you for the example file. Very inspiring.

                                  • 14. Re: How to sum tab-separated values?
                                    JavierDura

                                    Menno,

                                     

                                    Maybe I´ll have to deal with more that 50 columns!

                                    1 2 Previous Next