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

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

Fri, 21 Nov 2014 04:14:50 GMT

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

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

JonnyPoole — Fri, 21 Nov 2014 04:17:11 GMT

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 ?

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

Fri, 21 Nov 2014 04:23:01 GMT

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

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

danieloberbilli — Fri, 21 Nov 2014 04:37:04 GMT

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.

PrashantSangle — Fri, 21 Nov 2014 04:38:47 GMT

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

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

JonnyPoole — Fri, 21 Nov 2014 04:46:41 GMT

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

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

Fri, 21 Nov 2014 05:00:14 GMT

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] ,

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

danieloberbilli — Fri, 21 Nov 2014 05:08:24 GMT

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.

PrashantSangle — Fri, 21 Nov 2014 05:12:58 GMT

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

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

Fri, 21 Nov 2014 10:38:14 GMT

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.