topic Converting field to date in App Development

Converting field to date

RogerG — Thu, 30 Jun 2022 15:02:51 GMT

Hello all,

I am having an issue with dates, as is everyone.

I have a date loaded from a QVD, to which I don't have direct access.

When I load the date, it shows in the Data Model viewer as an integer.

After much searching and understanding, I got desperate and tried everything

[PA] : NoConcatenate Load Date(num#([LBE Start Date])), Date(num([LBE Start Date])), Date#(num#([LBE Start Date])), Date#(num([LBE Start Date])), Date([LBE Start Date]), Date#([LBE Start Date]), Date(num#([LBE Start Date]),'DD-MMM-YYYY'), Date(num([LBE Start Date]),'DD-MMM-YYYY'), Date#(num#([LBE Start Date]),'DD-MMM-YYYY'), Date#(num([LBE Start Date]),'DD-MMM-YYYY'), Date#([LBE Start Date],'DD-MMM-YYYY'), Date([LBE Start Date],'DD-MMM-YYYY'), Date(Date#(num#([LBE Start Date]),'DD-MMM-YYYY')), Date(Date#(num([LBE Start Date]),'DD-MMM-YYYY')), date(date#([LBE Start Date],'DD-MMM-YYYY')), Date(Date#(num#([LBE Start Date]),'DD-MMM-YYYY'),'DD-MMM-YYYY'), Date(Date#(num([LBE Start Date]),'DD-MMM-YYYY'),'DD-MMM-YYYY'), date(date#([LBE Start Date],'DD-MMM-YYYY'),'DD-MMM-YYYY'), Date#(Date(num#([LBE Start Date]),'DD-MMM-YYYY')), Date#(Date(num([LBE Start Date]),'DD-MMM-YYYY')), date#(date([LBE Start Date],'DD-MMM-YYYY')), Date#(Date(num#([LBE Start Date]),'DD-MMM-YYYY'),'DD-MMM-YYYY'), Date#(Date(num([LBE Start Date]),'DD-MMM-YYYY'),'DD-MMM-YYYY'), date#(date([LBE Start Date],'DD-MMM-YYYY'),'DD-MMM-YYYY'), *; Load * resident [TEMP PA];

I am still not able to pull a date field from this. In QS, my calendar date picker is not recognizing anything as a date.

Maybe I should give up coding 🙂

Re: Converting field to date

rubenmarin — Thu, 30 Jun 2022 15:46:51 GMT

Hi, if it's an integer, just Date(fieldName) should work.

Try loading this field in an empty app and add as a filter or in a table as dimension, if the values are left-aligned is a text, if it's right aligned it's a number.

All dates are numbers internally, each unit is a day: date(0) is 30/12/1899, Date(1) is 31/12/1899, Date(-1) is 29/12/1899 and 30/06/2022 is 44742. Decimals are used to set time so 44742.5 is 30/06/2022 at 12:00.

If the field looks like a number but is left aligned you need to onvert to number using Num#(), this depends on the formt of the number, you can post an image on how the data is shown on a table dimension.

Re: Converting field to date

Or — Thu, 30 Jun 2022 15:58:57 GMT

One other thing - if your dates are in the distant future or past, Qlik might not automatically flag the field as a date. In that case, you'll need to manually tag the field as a date, if memory serves the syntax is:

Tag Field [LBE Start Date] with '$date';

If that's not the issue, Ruben's post contains all of the steps to further investigate your issue.

Re: Converting field to date

RogerG — Thu, 30 Jun 2022 16:02:53 GMT

they are coming into the table as Dates, however the date picker and data model viewer disagree

this is my entire datamodel, this one date, everything else is commented out to see why the conversion is not functioning

Re: Converting field to date

RogerG — Thu, 30 Jun 2022 17:34:34 GMT

Thanks Or, this solved my issue, must be those historical dates