3 Replies Latest reply: May 15, 2014 10:04 AM by olap_bi _ RSS

    Count Rows

    olap_bi _

      I am hoping to get guidance in what I am trying to accomplish

       

      My record set is as follows

       

      LinkedEvent     AnyDescription

      L12345     Description # 1

      L12345     Description # 1

      L12345     Description # 1

      L12345     Description # 1

      L23456     Description # 2

      L23456     Description # 2

      L34567     Description # 3

      L45678     Description # 4

      L56789     Description # 5

       

      and my desired output ought to be

       

      Counter     LinkedEvent     AnyDescription

      1     L12345     Description # 1

      1     L12345     Description # 1

      1     L12345     Description # 1

      1     L12345     Description # 1

      2     L23456     Description # 2

      2     L23456     Description # 2

      3     L34567     Description # 3

      4     L45678     Description # 4

      5     L56789     Description # 5

       

      How do I go about working with this data-set, i.e. the desired Counter record

       

      Thanks, Ali

        • Count Rows
          Data Kruncher

          Hi Ali,

           

          I believe that you'll need three models to get this done.

           

          In the first model, create a summary with LinkedEvent as the only key field. Add a count as measure, but hide it. Make sure that you do not display subtotals or grand totals. Now export the summary.

           

          With a second model, use the exported summary as an input. In the Table window, add a calculated Counter field using RecNo() as the expression. Now export this table.

           

          Finally, reopen the orginal data source and model, and add an external lookup to retrieve the Counter value based on the LinkedEvent.

           

          HTH,

          Kruncher

            • Count Rows
              Olly Bond

              Hi Kruncher,

               

              I was having a look at this to try to bring it down to two models instead of three. If there was an option to export summaries with row numbers prefixed as a column it would be easy.

               

              Adding a calculated field to the table called Recno - formula is just Recno() - lets us add a measure to the summary called Min(Recno()) which returns (on this sample):

               

              L12345     1

              L23456     5

              L34567     7

              L45678     8

              L56789     9

              /CODE

               

              I'm having a look at formulae like Min(Recno)+Cum(Recno)-Max(Recno) to see if we can convert this to:

               

              L12345     1

              L23456     2

              L34567     3

              L45678     4

              L56789     5

              /CODE

               

              But there's a very limited range of aggregating functions available in the expression builder. Perhaps we should add this to the Future Features requests?

               

              Best wishes,

               

              Olly