Date Format - Timestamp

[engishfaque]

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

or

02/03/2015 12:11:22 AM

Kindly find attached file.

Kind regards,

Ishfaque Ahmed

[JonnyPoole]

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

[engishfaque]

Dear Jonathan,

Your answer is right, can you explain little bit?

Kind regards,

Ishfaque Ahmed

[Colin-Albert]

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

[engishfaque]

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

[JonnyPoole]

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

[engishfaque]

Any update? regards above attached screenshot.

[engishfaque]

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

[JonnyPoole · Accepted solution]

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