topic Re: Date Format not recognised when exporting to Excel in QlikView

Date Format not recognised when exporting to Excel

Thu, 12 Sep 2013 07:55:33 GMT

I have run some data in our company server and exported as QVD. I can reload the QVD into my Personal Desktop edition of QlikView and it is fine. I do this with about 70 individual QVD files using concatenate load. Sometimes when I export some data (in particular when I select WeekDay or Month aggregates, but also when I choose certain reports daily) some of the data rows are not recognized as dates in the date field. If for example I export a report from PE to Excel and data filter the column headers, when I drop down the date filter I can see year and months followed by individual days which are obviously not date recognized. If I deselect the years and delete the remainder it is fine, and only a small number of rows are affected. However, each individual file when loaded on its own works perfectly.

Because the incorrect dates are all throughout the data then it cannot be a single QVD file with errors. And because each QVD file works on its own I wouldn't think there is a problem with the original server data either. Also same data, same date range (full data set) one report is fine another has this date issue.

Does anyone have any idea why this happens? I have tested on two separate computers with same output.

Re: Date Format not recognised when exporting to Excel

marcus_sommer — Thu, 12 Sep 2013 08:09:21 GMT

I assume you load this files with a loop and perhaps not all datas in your sources are really date-values. QV takes the first row from a field to determine which datatype it is - not always is this right. I suggest you load this field with a format and/or convert statement like date(date#(yourdate, 'yourformat'), 'yourformat').

- Marcus

Re: Date Format not recognised when exporting to Excel

abeyphilip — Thu, 12 Sep 2013 08:12:12 GMT

Hi Darren,

Does date formats look similar in the front end before exporting? You can try explicitly making it date using - date() (number tab in case of expressions) and see if it helps.

Regards,

Abey

Re: Date Format not recognised when exporting to Excel

Thu, 12 Sep 2013 08:37:56 GMT

I just load it as *.qvd and there are daily data files for every day from 1st June to present. Given that only a small number of rows are affected I don't think that would be the issue.

Are you suggesting I change the Load script from

[Call Date],

Date(date#([Call Date],'DD-MM-YYYY'),'DD-MM-YYYY)

Re: Date Format not recognised when exporting to Excel

Thu, 12 Sep 2013 08:39:01 GMT

Yes, the date works fine in the Server based reports before exporting. Are you also suggest the same as Marcus above?

Re: Date Format not recognised when exporting to Excel

Thu, 12 Sep 2013 08:39:26 GMT

The -1- is an error by the way that shouldn't be there !