New row number, per date

[johngouws]

Good morning.

I have a RowNo for all transactions but would like to have a RowNo at a date level. This will give me a RowNo '1' for every date. In a 23 day month I will have 23 RowNo 1's and so on. 

The below script gives me a Daily_Row '1' for the first of every date and the first month is correct. But the rest of the months are not correct for RowNo 2 on wards. 

Thank you very much - I appreciate your assistance - stay safe...

t1:
LOAD * INLINE [
Date, Row
2020/01/01, 1
2020/01/01, 2
2020/01/01, 3
2020/01/01, 4
2020/01/01, 5
2020/01/01, 6
2020/01/01, 7
2020/01/01, 8
2020/01/01, 9
2020/01/01, 10
2020/01/01, 11
2020/01/01, 12
2020/01/02, 13
2020/01/02, 14
2020/01/02, 15
2020/01/02, 16
2020/01/02, 17
2020/01/02, 18
2020/01/03, 19
2020/01/03, 20
2020/01/03, 21
2020/01/03, 22
2020/01/03, 23
2020/01/03, 24
2020/01/03, 25
2020/01/03, 26
2020/01/03, 27
2020/01/03, 28]
;

t2:
Load
*,
IF(Date = Previous(Date), Previous(Row) +1, 1) AS Daily_Row
Resident t1
Order By Date ASC;

Drop Table t1;

[ArnadoSandoval · Accepted solution]

Hi @johngouws 

You should be using Peek() to look at the previous row, and implement the running total, like this:

IF(Date = Peek(Date), Peek(Daily_Row) +1, 1) AS Daily_Row

 Hope this helps

[tresesco]

Try:

AutoNumber(RowNo(), Date) as RowNew 

[johngouws]

Hi @ArnadoSandoval .

Thank you - perfect.