Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Before you can operate with your dates, you need to convert the string to the proper timestamp format, using the function timestamp#().
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
Did the previous solution not work? Curious if you had a different angle to address ...
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
Any update?
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
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)';