topic Re: Problem editing Dates in QlikView

Problem editing Dates

Tue, 14 Jan 2014 16:28:00 GMT

Hello,

Im currently loading a file from excel that has a colum of Date with this format 08.01.2014 21:13, and im not sure how to edit this on the script so i only have the Date without the hour. Or even maybe a field with Date and another with hour, or a field that i can use Week(Date) and even if it has the hour it'll work.

I've currently tryed to use Date(field,'DD/MM/YYYY') and it doesn't work.

Anyone can knows how to do this?

thanks,

Re: Problem editing Dates

Tue, 14 Jan 2014 16:48:20 GMT

Maybe try date(date#(field, 'DD.MM.YYYY hh:mm'), 'DD/MM/YYYY')

Re: Problem editing Dates

Nicole-Smith — Tue, 14 Jan 2014 17:00:35 GMT

I'm not sure if you can have a time stamp in the date#() function. You may need to use (and note that the floor will take the time stamp away):

date(floor(timestamp#(YourField, 'DD.MM.YYYY hh:mm')), 'DD/MM/YYYY')

Re: Problem editing Dates

Tue, 14 Jan 2014 17:03:47 GMT

Date:

LOAD * INLINE

[

Date

08.01.2014 21:13

];

Final:

Load

date(date#(Date, 'DD.MM.YYYY hh:mm'), 'DD/MM/YYYY') as FinalDate

RESIDENT Date;

DROP TABLE Date;

This works so I believe date#() recognizes a time stamp.

Re: Problem editing Dates

jaimeaguilar — Tue, 14 Jan 2014 17:51:45 GMT

I think this is not the most elegant solution but it works:

date(date#(left(TimestampField, 10), 'DD.MM.YYYY'), 'DD/MM/YYYY')