Increment value based of if condition, without autonumber or autogenerate

[Woojtek_96]

Hi,

I have table with dates, and want to create new column with the same numbers if date  = peek(date, -1)  if not, increment number by one 1, just like on table below. i dont want to user autogenerate, or autonumber becouse of performance.
I had searched many topic, maybe I didn't find exact solution andso on I decided to write own question.
I was trying with variable but it didn't work

Date	Number
2022-01-01	1
2022-01-01	1
2022-01-01	1
2022-01-02	2
2022-01-02	2
2022-01-03	3
2022-01-04	4
2022-01-04	4
2022-01-04	4

Thank for all help!

Best regards
Wojtek

[brunobertels]

hi 

if you need to populate a field by number depending of the date : 

as 2022 -01-01 as value 1 and 2022-01-02 as vale 2 you can use day() 

day(Date)

if the increment is day in year use daynumberofyear()

daynumberofyear(Date)

[Woojtek_96]

Unfortunately i could have dates with wide range and no day by day. For example:
2021-06-06,
2021-05-08,
2021-05-08,
2021-05-08,
2021-04-02,
2021-04-02,

[marcus_sommer]

Please share more details on which data-sources and where, how and why these information should be created - and why autonumber caused performance-issues.

- Marcus

[Woojtek_96]

@marcus_sommer 

Hi Marcus, 
autonumber would cost too much time for us, because we have millions of records to calculate. We have some experience with autogenerate function and it was always too much time consuming for us. 

We need to achieve results like these below. 

cookie id	date	autonumber
1	2022-05-16	1
1	2022-05-16	1
1	2022-05-16	1
1	2022-04-15	2
1	2022-04-15	2
1	2022-04-15	2
1	2022-03-14	3
1	2022-03-14	3
1	2022-03-14	3
1	2022-03-12	4
1	2022-03-12	4

[marcus_sommer]

Please elaborate what you are doing in detail - means how does the load-statement look like?

- Marcus