Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
May be like this
LOAD Date,
Year,
period,
AutoNumber(RowNo(), period) as Counter
FROM ....;
or
AutoNumber(RecNo(), period) as Counter
Try like:
Load
Autonumber(Rowno(), Period) as Counter
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
thanks both, i have tried this but the counter is resetting at the start of every period..
Isn't that what you wanted? Reset every period? If not, what are you expecting to see?
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!
I guess this:
AutoNumber(RecNo(), Year(Date)) as Counter
May be even simpler like:
Load
Autonumber(Period) as Counter
or if you want it to increment each Month and Year
AutoNumber(RecNo(), MonthName(Date)) as Counter