3 Replies Latest reply on Jun 14, 2014 7:29 AM by Imre...

    See top-ten or sort on highest column-count

    Imre...

      Title

      See top-ten or sort on highest column-count

      Post

           Hi, 

           weekly I receive a .csv with error-code-columns. When the cells are empty, the error is not present. When the cells reads "Ja", the error code is present.

           I know how to make a count per column but what I cannot yet do is: make Filemaker show me the error code with the highest count. 

            

           In other words:

           the screenshot shows 7 columns. Column dbf_3305 _F_ has a total of zero for the value "Ja", column dbf_3308 _F_ has a total of -lets say- 30, column dbc_3311 _F_ has a total of 18. Since the source file has many many columns, it is impossible to see which has the highest score. 

           I 'd like FileMaker to show me, sorted by score-count, the list of error codes. So in this screenshot-example column "dbf_3308 _F_" would be number one and "dbf_3311 _F_" number two, etc. 

            

           Can someone help me please?

            

            

      Schermafbeelding_2014-06-14_om_10.29.30.jpg

        • 1. Re: See top-ten or sort on highest column-count
          WBSimon

               I usually add a top count list (5, 10, etc) by using a portal. Confine the number of rows of the portal to the top count you want with no scroll bar, sort the portal by your 'score-count' and change to sort order to 'descending'.

          • 2. Re: See top-ten or sort on highest column-count
            philmodjunk

                 It  would appear that you need to sort on fields (columns) rather than records (rows)

                 You want the field/column with the highest error count.

                 Restructuing this data (a process that can be scripted after import) to divide this data up so that each cell is an individual record might be the simplest way to do this as you could then group your records by error code via sorting and then re-order the sorted groups by count in descending order to put the codes with the highest count at the top of the report.

                 What does one entire row represent in this data? is there some kind of identifier column--perhaps in the first column that uniquely identifies each row of data? If there is, is it important?

                 What I am imagining is a looping script with two nested loops. The inner loop loops through your columns while your outer loop loops through rows. The script would create one record for each error code where there is a "ja" in the cell. A field records the error code (the column name in this data ) and that is all that is needed unless you also need to link all data from the same row by some Identifier imported as part of this data.

            • 3. Re: See top-ten or sort on highest column-count
              Imre...

                   Tnx Phil.

                    

                   Pondering about it I guessed I could try to accomplish it by means which you just described. Hoped for an easier option.

                   Tnx, I'll try this.