date in qlikview shows d/mm/yyyy 0:00:00

[chriscools]

Hello,

i load some data from an acces query into qlikview.

one of the fields is a data field, even though i have date formatted in acces ass D/MM/YYYY,

it is imported in qlikview as d/mm/yyyy 0:00:00

in qlikview i made a pivot table and it's a rapport i have to mail every day in excel.

i can't find how to adapt the date in qlikview so the hour falls of and when i export it to excel i can not just format the date,

because apparantly it is not recognized as a date.

is there a way that i can directly change the appereance in qlikview?

i included the excelfile.

can someone help me with this?

thanx!

grtz,

chris

[Anonymous]

Hi Chris,

When you have a date in a source (Access for example) with a certain format, let's say YYYY-MM-DD and you want to make sure that QlikView interprets it correctly, you should use the Date#() function. This function is used to show QlikView how to interpret a value as a date while Date() is used to display a properly identified date in a certain way.

So in the case above, we would use Date#(DateValue, 'YYYY-MM-DD') to let QlikView know that the values in DateValue are formatted in the YYYY-MM-DD format. If you then want to display it in another format, for example 15-JUN-2011, you can use the Date() outside the Date#() function to cast it in the new format:

Date(Date#(DateValue, 'YYYY-MM-DD'), 'DD-MMM-YYYY')

Hope this makes sense.

[danielrozental]

If your problem is exporting the information to excel you can try checking/unchecking the "Use regional settings when sending to export" in user prefences>export tab.

[fidaiazeem]

Dear Chris,

while loading the data in qlikview use the following function to load the date field:

Date(Floor(Datefield_name),'D/MM/YYYY') AS Datefield_Name

You wont get the timestamp when u view it in ur charts.

Regards,

Azeem

[pgalvezt]

Hi Chris:

Below I left the solution to your question. I know that QlikView Import date with time. So in that case you have to set just the time with format in this case with hh:mm:ss.

Hope this help you.

Test:
LOAD * INLINE [
    DATA
    02-09-2011 0:00:00
    02/05/2011 0:00:00
];

Test2:
load
IF( SubStringCount(DATA, '-')> 0,
      DATE(DATE#(DATA,'MM-DD-YYYY hh:mm:ss'),'DD-MM-YYYY'),
      DATE(DATE#(DATA,'DD/MM/YYYY hh:mm:ss'),'DD-MM-YYYY')) as DataActual
resident Test;  

Test3:
load
IF( SubStringCount(DATA, '-')> 0,
      DATE(DATE#(DATA,'MM-DD-YYYY hh:mm:ss'),'MMMM DD'),
      DATE(DATE#(DATA,'DD/MM/YYYY hh:mm:ss'),'MMMM DD')) as DataActual1
resident Test;

[#6]

A simple but effective solution could be using the Date Function in your Load Script.

i.e: Date([YourDateFromAccess],'D/MM/YYYY') AS [WhatEverYouWantToCallIt]

[chriscools]

Hey,

indeed this seems by far the easiest solution, but when i try it, i get a an error pop-up:

this is my date function:

date([datum],'D/MM/YYYY') as [datumGepicktPerDag]

but then it says that i use a wrong number of arguments...

what would be wrong?

grtz

[pgalvezt]

Hi Chris:

Below I left the solution to your question. I know that QlikView Import date with time. So in that case you have to set just the time with format in this case with hh:mm:ss.

Hope this help you.

Test:
LOAD * INLINE [
    DATA
    02-09-2011 0:00:00
    02/05/2011 0:00:00
];

Test2:
load
IF( SubStringCount(DATA, '-')> 0,
      DATE(DATE#(DATA,'MM-DD-YYYY hh:mm:ss'),'DD-MM-YYYY'),
      DATE(DATE#(DATA,'DD/MM/YYYY hh:mm:ss'),'DD-MM-YYYY')) as DataActual
resident Test;  

Test3:
load
IF( SubStringCount(DATA, '-')> 0,
      DATE(DATE#(DATA,'MM-DD-YYYY hh:mm:ss'),'MMMM DD'),
      DATE(DATE#(DATA,'DD/MM/YYYY hh:mm:ss'),'MMMM DD')) as DataActual1
resident Test;