Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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

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);

1 Solution

Accepted Solutions
danieloberbilli
Valued Contributor II

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

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

9 Replies
Employee
Employee

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

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 ?

Not applicable

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

11-18-2014 5-43-14 PM.jpg

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
Valued Contributor II

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

Try DATE#() instead of DATE()

Date#  interprets the value as date

Date is just formatting

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

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

Regards,
Prashant Sangle
Employee
Employee

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

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 ....

Not applicable

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

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
Valued Contributor II

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

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

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

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

Regards,
Prashant Sangle
Not applicable

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

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.

Community Browser