Convert the inflated values in the date field.

[appi_priyakarna]

There's a date field that has some inflated values like 8/15/57263 and similarly there are ~1000+ records. Along with these inflated values there's also values coming in format of MM/DD/YYYY. When taken a list box and exported this field to excel the values with 8/15/57263 comes as ########### and when hover over, it says 'Dates and times that are negative and too large are shown as ####### . On the table viewer this date field is integer. I want to change these inflated values to date format of 'MM/DD/YYYY' and also keep the other values too in the field. Any help is appreciated.

[BrunPierre]

it's possibly being interpreted as a numeric value rather than a date. If the numeric value is too large or doesn't correspond to a recognizable date format, It may look like a random sequence of numbers.

What format are the dates in the source table?

[rwunderlich]

I second what @BrunPierre said. You likely have mixed data formats in your source file, some MM/DD/YYYY and some YYYYMMDD. A date of "8/15/57263" in Qlik would look like a number about 2022nnnn in your source file. 

-Rob

[appi_priyakarna · Accepted solution]

Hi Brun,

In the source table the date formats currently are in YYYYMMDD format. So, from the source table which is an oracle data source the data is saved in the qvd wherein from 2018 it's in MM/DD/YYYY format but from Feb 1st, 2022, it started generating in the YYYYMMDD and that's where it started an issue.

The source is generating the data and having it saved in the qvd and then in other qvw it's concatenating to a historical qvd (the historical qvd/table is being created by the above same source table). This historical qvd when used in the data model it shows up inflated values.

[appi_priyakarna · Accepted solution]

I used the syntax as date(alt(date#(datefield,'YYYYMMDD'),date(datefield,'YYYYMMDD'))) and it worked. Found it here on Solved: mixed date format in one column - Qlik Community - 1739051