DATE field not reading QV when I load multiple excel sheet method.

[#1]

Hi QV Team,

Here is my code to load multiple excel sheets from Sales folder. It worked fine but only problem is DATE field is not reading properly.I'm getting 01/01/1900 or 01/01/1899 format. Pls someone help me out. If there is anyother way to ready all the Excel Sheets and getting correct DATE field.

TEMP:

LOAD

filetime()                                           as file_time,

filepath()                                           as file_path,

filebasename()                                       as z_file,

timestamp(filetime())                                as time_stamp

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

left join (TEMP)

load

max(timestamp(file_time))                             as max_time

resident TEMP;

FINAL:

LOAD *, 1 as flag

resident TEMP where time_stamp=max_time;

drop table TEMP;

SAPsalesreport:

LOAD

[Sales Doc.]as [Sales Order #],

  DATE(Req.dlv.dt, 'MM/DD/YYYY') as [Request Delivery DATE] ,

  DATE(Req.dlv.dt, 'MM/DD/YYYY') as [Delivery Testing Date] ,

  DATE(Dlv.Date, 'MM/DD/YYYY') as [Delivery DATE] ,

  DATE(Dlv.Date, 'MM/DD/YYYY') as [Delivery Test Date2] ,

  [Prod order]

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

[JonnyPoole]

That would happen if qlikview did not recognize the source data as dates.

If you load just 'Req.dlv.dt' in a new qlikview app from the same source file, what does the data values  look like ?

[#3]

Here is my Source Date Field.  I'm getting 01/01/1900 or 01/01/1899 format with no change even before or after trying to convert it to DATE format.

Pls Help me !!

[danieloberbilli]

Try DATE#() instead of DATE()

Date#  interprets the value as date

Date is just formatting

[PrashantSangle]

Hi,

Use Date#()

then use Date()

Try like,

SAPsalesreport:

LOAD

[Sales Doc.]as [Sales Order #],

  Date(DATE#(Req.dlv.dt, 'MM/DD/YYYY'),'MM/DD/YYYY') as [Request Delivery DATE] ,

  Date(DATE#(Req.dlv.dt, 'MM/DD/YYYY'),'MM/DD/YYYY') as [Delivery Testing Date] ,

  Date(DATE#(Dlv.Date, 'MM/DD/YYYY'),'MM/DD/YYYY') as [Delivery DATE] ,

  Date(DATE#(Dlv.Date, 'MM/DD/YYYY'),'MM/DD/YYYY') as [Delivery Test Date2] ,

  [Prod order]

FROM

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

Regards

[JonnyPoole]

It doesn't look obvious except that the dates are in M/D/YYYY which shouldn't really be an issue.

To make qlikview explicitly recognize the format use

Date(Date#( Req.dlv.dt , 'M/D/YYYY')) as ....

If you want the date to show in the app as MM/DD  (instead of single digits where applicable)

Date(Date#( Req.dlv.dt , 'M/D/YYYY'),'MM/DD/YYYY') as ....

[#7]

still No luck,

I'm not getting any values on dashboard when I use this script.

I'm getting "(unavailable) [Request Delivery DATE]" ......... list box.

Date(DATE#(Req.dlv.dt, 'MM/DD/YYYY'),'MM/DD/YYYY') as [Request Delivery DATE] ,

  Date(DATE#(Req.dlv.dt, 'MM/DD/YYYY'),'MM/DD/YYYY') as [Delivery Testing Date] ,

  Date(DATE#(Dlv.Date, 'MM/DD/YYYY'),'MM/DD/YYYY') as [Delivery DATE] ,

  Date(DATE#(Dlv.Date, 'MM/DD/YYYY'),'MM/DD/YYYY') as [Delivery Test Date2] ,

[danieloberbilli · Accepted solution]

could it be that your date is actually stored in a complete different format...see the column A7 of your excel screenshot. Maybe clarify whats the actual input data format

[PrashantSangle]

Hi,

Are you performing any other operation after this conversion.

Looks like  [Request Delivery DATE]   is dropped.

What about [Delivery Testing Date] field It is also unavailable or not.

If possible then post SALESREPORT_111.XLS

Regards

[#10]

thank you soooooooooooooooo much.

Even though it is a Excel Sheet, that is some different format. while loading the Excel Sheet I did proper transform option and renamed the field there itself and finished it. Now with out using the date converion.

*****Date(Date#( Req.dlv.dt , 'M/D/YYYY'),'MM/DD/YYYY') **** 

I'm getting the proper format as such it was on excel sheet.