topic Re: Date Format - Timestamp in QlikView

Date Format - Timestamp

engishfaque — Thu, 05 Mar 2015 00:01:51 GMT

Dear All,

I'm trying to extract Max Date from attached file in a load script.

Here is my script:

Floor(Max(TimeStamp#([date/time], 'DDMMMYYY:hh:mm:ss'))) AS MaxDate

But when I reload the script then nothing is coming. My current input and required output is given below.

Current Date Format:

date/time

Mar 2, 2015 12:11:11 AM PST

Mar 2, 2015 12:11:22 AM PST

Required Output:

02/03/2015

02/03/2015 12:11:22 AM

Kindly find attached file.

Kind regards,

Ishfaque Ahmed

Re: Date Format - Timestamp

JonnyPoole — Thu, 05 Mar 2015 00:46:36 GMT

had to strip off the timezone with a mid() function. the rest you are on the right track:

Load

Timestamp(DateTime,'DD/MM/YYYY hh:mm:ss tt') as DateTime,

Date( floor(DateTime),'DD/MM/YYYY') as Date;

LOAD

Timestamp#( mid([date/time],1, len([date/time])-4) ,'MMM D, YYYY hh:mm:ss tt') as DateTime,

[settlement id],

type,

[order id]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Re: Date Format - Timestamp

engishfaque — Thu, 05 Mar 2015 17:05:25 GMT

Dear Jonathan,

Your answer is right, can you explain little bit?

Kind regards,

Ishfaque Ahmed

Re: Date Format - Timestamp

Colin-Albert — Thu, 05 Mar 2015 17:23:24 GMT

This will extract the date and time portions separately

date(floor(date#(replace(date_value, 'PST', ''), 'MMM D, YYYY hh:mm:ss TT')))

and this will extract the time

time(frac(date#(replace(date_value, 'PST', ''), 'MMM D, YYYY hh:mm:ss TT')))

or for both combined

timestamp(date#(replace(date_value, 'PST', ''), 'MMM D, YYYY hh:mm:ss TT'))

MMM is Month

D is day of Month

YYYY is year

hh is hour

mm is minute

ss seconds

TT is AM or PM

The replace() removes the PST timezone

date# and time# convert the string values to a date or time respectively

Re: Date Format - Timestamp

engishfaque — Thu, 05 Mar 2015 17:28:34 GMT

Dear Jonathan,

When I'm going to store MaxDate field into variable using Let, then it's returning me decimal number instead of Max Date, such as illustrated in attached screenshot.

Here is a variable logic,

// Find Last Modified Date - Preceding Load

LOAD //Timestamp(MaxDateTimestamp,'DD/MM/YYYY hh:mm:ss tt') as [date/time], // If you need Max Date with timestamp then uncomment this line

Max(Date(Floor(MaxDateTimestamp),'DD/MM/YYYY')) as MaxDate

Group By MaxDateTimestamp;

// Loading Data from QVD

Last_Updated_Date:

LOAD Max(Timestamp#(Mid([date/time], 1, Len([date/time])-4),'MMM D, YYYY hh:mm:ss tt')) as MaxDateTimestamp

FROM

[$(vFolderSourceData)QVDs\Sales.qvd]

(qvd);

// Store Last Modified Date to a Variable

LET Last_Updated_Date = Peek('MaxDate', 0, 'Last_Updated_Date');

// Drop Table Sales

DROP Table Last_Updated_Date;

Kind regards,

Ishfaque Ahmed

Re: Date Format - Timestamp

JonnyPoole — Thu, 05 Mar 2015 17:47:58 GMT

Sure,

The first part is this expression. I'm using mid() function to strip off the last 4 characters, in this case ' PST'. Mid() will take the first characters up to the date length - 4.

mid([date/time],1, len([date/time])-4)

so: Mar 2, 2015 12:11:22 AM PST

turns into: Mar 2, 2015 12:11:22 AM

Then i interpret it as a timestamp which stores date and time . Timestamp# is an interpretive function so you use MMM for 'jan' , D for single or double digit days like '1' or '12' (not 01) and so forth. 'tt' means am or pm.

Timestamp#( mid([date/time],1, len([date/time])-4) ,'MMM D, YYYY hh:mm:ss tt')

the result needs to be formatted for output.

In the preceding load i format i 2 ways:

Timestamp(DateTime,'DD/MM/YYYY hh:mm:ss tt') as DateTime ... renders as '02/03/2015 12:11:22 AM'

And then here i use floor() which rounds the date down to a whole number. This is because dates in qlik are numeric and dates with times are numbers with decimals. So if you want to strip off the time, use floor()

Date( floor(DateTime),'DD/MM/YYYY') as Date .... renders as '02/03/2015'

Any help ? See the attached to help you with the D , M , Y etc..

Re: Date Format - Timestamp

engishfaque — Thu, 05 Mar 2015 19:10:41 GMT

Any update? regards above attached screenshot.

Re: Date Format - Timestamp

engishfaque — Thu, 05 Mar 2015 19:27:20 GMT

Dear All,

When I'm using following Let statment, then it's returning me number instead of date.

LET Last_Updated_Date = date#(Floor(Peek('MaxDate', 0, 'Last_Updated_Date')), 'DD/MM/YYYY');

Current out is 42065 instead of 02/03/2015

Kind regards,

Ishfaque Ahmed

Re: Date Format - Timestamp

JonnyPoole — Thu, 05 Mar 2015 20:18:15 GMT

For the variable use case , you need to apply Date() and Timestamp() after the max. So i changed my sample below to do that and capture the maxes.

Dates:

Load

Timestamp(DateTime,'DD/MM/YYYY hh:mm:ss tt') as DateTime,

Date( floor(DateTime),'DD/MM/YYYY') as Date;

LOAD

Timestamp#( mid([date/time],1, len([date/time])-4) ,'MMM D, YYYY hh:mm:ss tt') as DateTime,

[settlement id],

type,

[order id]

FROM

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

MaxDate:

load Timestamp(max(DateTime),'DD/MM/YYYY hh:mm:ss tt') as maxDateTime,

Date(max(Date),'DD/MM/YYYY') as maxDate

Resident Dates;

let vMaxDateTime=peek('maxDateTime',0,'MaxDate');

let vMaxDate=peek('maxDate',0,'MaxDate');