counter field on period

[hopkinsc]

Hi all,

i have a calendar table like below

Date, Year, period

01/01/2016, 2016, 1

02/01/2016, 2016, 1

...... all the way to 31/01/2016

01/02/2016, 2016, 2

02/02/2016, 2016, 2

and so on

i am trying to create a counter field in the script but only to increment when the period changes.

if i use rowno() then this will increment on each row.

can anyone help please?

[sunny_talwar]

May be like this

LOAD Date,

          Year,

          period,

          AutoNumber(RowNo(), period) as Counter

FROM ....;

or

AutoNumber(RecNo(), period) as Counter

[tresesco]

Try like:

Load

          Autonumber(Rowno(), Period) as Counter

[sunny_talwar]

Or you can also try this:

Table_Temp:

LOAD Date,

          Year,

          period

FROM ....;

Table:

LOAD *,

           If(period = Previous(period), RangeSum(Peek('Counter'), 1), 1) as Counter

Resident Table_Temp

Order By period, Date;

DROP Table Table_Temp;

The first option looks sleep and have less number of rows, but I have seen that performance wise peek/previous tends to give better performance.

Best,

Sunny

[hopkinsc]

thanks both, i have tried this but the counter is resetting at the start of every period..

[sunny_talwar]

Isn't that what you wanted? Reset every period? If not, what are you expecting to see?

[hopkinsc]

no, what i need is a constant count. so i want the count to increase by 1 on every period chnage, so something like this..

Date, period, counter

01/01/2016, 201601, 1

02/01/2016, 201601, 1

and so on

01/02/2016, 201602, 2

02/02/2016, 201602, 2

and so on.

is that more clear? apologies i can see how my original question wasn't clear enough!

[sunny_talwar]

I guess this:

AutoNumber(RecNo(), Year(Date)) as Counter

[tresesco · Accepted solution]

May be even simpler  like:

Load

          Autonumber(Period) as Counter

[sunny_talwar]

or if you want it to increment each Month and Year

AutoNumber(RecNo(), MonthName(Date)) as Counter