Autonumber Function on Date Field is not returning required valu

Ron1 — Tue, 28 Jan 2020 15:29:09 GMT

I am facing a very strange issue and I am unable to find a solution for the same.

Your help will be highly appreciated

I am using auto function on my date field. However during year transition it is not showing consistency.Please help me to fix this.

Date	Autonumber
05.12.2019	31
12.12.2019	32
19.12.2019	33
26.12.2019	35
02.01.2020	34

You cal also refer the attached screenshot

Re: Autonumber Function on Date Field is not returning required valu

andoryuu — Tue, 28 Jan 2020 16:33:58 GMT

Autonumber will assign numbers based on the order that it reads data each time. You have to read in your dates as date values and then order by that date value. In the code below Table1 has your base data (in the wrong order on purpose) and assigns the "wrong" number. Table2 orders by the date field (that is brought in as text) and gives the "wrong" result. Table3 is what you want as the value is converted using date#() and then ordered by that field.

Table1: LOAD DateValue, FormattedDate, AutoNumber(DateValue,'Table1DateValue') as AutonumberOfDateValue, AutoNumber(FormattedDate,'Table1FormattedDate') as AutonumberOfFormattedDate; load DateValue, Date#(DateValue,'dd.mm.yyyy') as FormattedDate inline [ DateValue 05.12.2019 12.12.2019 19.12.2019 02.01.2020 26.12.2019 ]; NoConcatenate Table2: LOAD DateValue as OrderedDateValueByText, FormattedDate as OrderedFormattedDateByText, AutoNumber(DateValue,'Table2DateValue') as OrderedAutonumberOfDateValueByText, AutoNumber(FormattedDate,'Table2FormattedDate') as OrderedAutonumberOfFormattedDateByText Resident Table1 ORDER BY DateValue; NoConcatenate Table3: LOAD DateValue as OrderedDateValueByDate, FormattedDate as OrderedFormattedDateByDate, AutoNumber(DateValue,'Table3DateValue') as OrderedAutonumberOfDateValueByDate, AutoNumber(FormattedDate,'Table3FormattedDate') as OrderedAutonumberOfFormattedDateByDate Resident Table1 ORDER BY FormattedDate;

Re: Autonumber Function on Date Field is not returning required valu

Ron1 — Thu, 30 Jan 2020 13:48:41 GMT

Yes in my data based on load order it was not consistent.

Hence I applied order by clause on my date field, then applied auto number.

Which fixed the issue

topic Re: Autonumber Function on Date Field is not returning required valu in App Development

Autonumber Function on Date Field is not returning required valu

Re: Autonumber Function on Date Field is not returning required valu

Re: Autonumber Function on Date Field is not returning required valu