PST - MaxDate

[engishfaque]

Dear All,

I'm trying to find MaxDate from date field where data consist on PST date format, but some issue in format.

Output:

Mar 3, 2015 12:11:11 AM PST

Kindly find attached sample file.

Kind regards,

Ishfaque Ahmed

[datanibbler]

Hi Ishfaque,

is that actually a date_format or is it loaded into QlikView as a String (you can see if you load it, then go to the table_viewer and hover on the field.

If it is a string, you can cut out the first segments and then turn it into a date using DATE(MAKEDATE()).

HTH

[Oleg_Troyansky]

Before you can operate with your dates, you need to convert the string to the proper timestamp format, using the function timestamp#().

[engishfaque]

Dear DataNibbler,

I have to use this complete format, because I'm going to make incremental load.

No matter, either it's string or date, but I have to store MaxDate into variable.

Kind regards,

Ishfaque Ahmed

[JonnyPoole]

Did the previous solution not work?  Curious if you had a different angle to address ...

Date Format - Timestamp

[engishfaque]

Dear Jonathan,

Previous solution is working fine as I already marked correct as you noticed that.

Now, I'm making incremental load module where I need date/string including PST text as I can place same variable as in where condition.

Once I'll store Date with PST in variable then I'll use it directly in where condition without making any change.

Here I would like to clear that, I have an alternate approach to do this, such as first of all I remove PST text from data source file after that I perform where condition.

But it's not long time solution, because each day I have to reload my dashboard.

Looking forward to your valuable reply.

Kind regards,

Ishfaque Ahmed

[engishfaque]

Any update?

[engishfaque]

Dear All,

Listed below script is giving me result without PST,

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

Current Output:

02/03/2015 11:59:46 PM

Required Output:

02/03/2015 11:59:46 PM PST

Here is the complete script where I want to use PST format as a field,

IncrementalLoad:

LOAD [date/time],

  Timestamp#(Mid([date/time], 1, Len([date/time])-4),'MMM D, YYYY hh:mm:ss TT') as DateTimeCorrectFormat //store date without PST text

FROM

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

Where [date/time] > $(vMaxDateTime);

Currently Where clause values looks like,

Where '02/03/2015 11:59:46 PM PST' > '02/03/2015 11:59:46 PM'

Required Output:

Where '02/03/2015 11:59:46 PM PST' > '02/03/2015 11:59:46 PM PST'

Kind regards,

Ishfaque Ahmed

[JonnyPoole]

The following load script (new part at bottom) will generate the following SQL.  Is that what you are after ? 

SQL Produced:

SQL SELECT *

FROM DateTimeTable

where DateTime= 'Mar 2, 2015 12:11:22 AM PST'

Load Script:

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

let vMaxDateTimeSQLFilter =  date(vMaxDateTime,'MMM D, YYYY hh:mm:ss TT') & ' PST';

//

ODBC CONNECT TO [DateTimeTable;DBQ=C:\Temp\Database71.accdb];

LOAD ID,

    `DateTime`;

SQL SELECT *

FROM DateTimeTable

where DateTime= '$(vMaxDateTimeSQLFilter)';