Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Ron1
Partner
Partner

Autonumber Function on Date Field is not returning required valu

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. 

DateAutonumber
05.12.201931
12.12.201932
19.12.201933
26.12.201935
02.01.202034

You cal also refer the attached screenshot

 
2 Solutions

Accepted Solutions
andoryuu
Creator III
Creator III

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;

View solution in original post

Ron1
Partner
Partner
Author

 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

View solution in original post

2 Replies
andoryuu
Creator III
Creator III

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;

View solution in original post

Ron1
Partner
Partner
Author

 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

View solution in original post