topic Re: PST - MaxDate in QlikView

PST - MaxDate

engishfaque — Fri, 06 Mar 2015 14:36:57 GMT

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

Re: PST - MaxDate

datanibbler — Fri, 06 Mar 2015 14:49:58 GMT

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

Re: PST - MaxDate

Oleg_Troyansky — Fri, 06 Mar 2015 14:51:08 GMT

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

Re: PST - MaxDate

engishfaque — Fri, 06 Mar 2015 14:53:52 GMT

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

Re: PST - MaxDate

JonnyPoole — Fri, 06 Mar 2015 15:14:39 GMT

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

Date Format - Timestamp

Re: PST - MaxDate

engishfaque — Fri, 06 Mar 2015 16:22:48 GMT

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

Re: PST - MaxDate

engishfaque — Fri, 06 Mar 2015 18:22:14 GMT

Any update?

Re: PST - MaxDate

engishfaque — Fri, 06 Mar 2015 18:47:18 GMT

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

Re: PST - MaxDate

JonnyPoole — Fri, 06 Mar 2015 18:47:49 GMT

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