Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Date field that includes day and time stamp

Dear QV users,

I have problems with extracting a date stamp in a field that has a date and time stamp. The field is called RealizedIBReady. I included a table as an attachment.

I tried the MakeDate function:

MakeDate(Mid(left(RealizedIBReady,4),7,4),Mid(left(RealizedIBReady,4),4,2),Mid(left(RealizedIBReady,4),1,2)) as RealizedIBReadyDay

Data is extracted from a QVD file. I have imported the field with a left join into the exciting file.

regards,

Aissam

1 Solution

Accepted Solutions

Re: Date field that includes day and time stamp

Qlikview recognized the RealizedBReady field automatically as a timestamp value when I loaded the excel file. No conversion was necessary.

If you want to cut off the time part of a timestamp value you can use the floor function: floor(MyTimeStampField). That returns a number that represents the date, so you may want to format it as a date with the date() function: date(floor(MyTimeStampField), 'DD-MM-YYYY')


talk is cheap, supply exceeds demand
4 Replies

Re: Date field that includes day and time stamp

Qlikview recognized the RealizedBReady field automatically as a timestamp value when I loaded the excel file. No conversion was necessary.

If you want to cut off the time part of a timestamp value you can use the floor function: floor(MyTimeStampField). That returns a number that represents the date, so you may want to format it as a date with the date() function: date(floor(MyTimeStampField), 'DD-MM-YYYY')


talk is cheap, supply exceeds demand
Not applicable

Re: Date field that includes day and time stamp

Hello Gysbert,

That's true. The problem I have is that you have a date and a time stamp. If the same date has different timestamps, the line chart shows this separate instead as a total for a whole day.

regards,

Aissam

Re: Date field that includes day and time stamp

Hi,

But if you use Floor() as Gysbert Suggest. YOu no need to worry about time stamp.

Regards

Great dreamer's dreams never fulfilled, they are always transcended.

Re: Date field that includes day and time stamp

Hi,

Your date field RealizedIBReady is in proper timestamp date format also try to load

LOAD JOB_NUMBER,

RealizedIBReady,

Timestamp( (RealizedIBReady) ) as TestRealizedIBReady,

Year( Timestamp( (RealizedIBReady) )) as Year,

Month( Timestamp( (RealizedIBReady) )) as Month,

Day( Timestamp( (RealizedIBReady) )) as Day,

 

MakeDate(Year( Timestamp( (RealizedIBReady) )),

Month( Timestamp( (RealizedIBReady) )),

Day( Timestamp( (RealizedIBReady) ))) as Date

FROM

[14-1022 tabel date stamp.xlsx]

(ooxml, embedded labels, table is Sheet1);

Regards,

Anand

Community Browser