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

# Count Rows

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

My record set is as follows

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

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

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

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

• ###### Count Rows

Thank you to every one !!!!!!

It worked, Ali