Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted

Re: max date

Has your date / timestamp data been loaded in the correct format or has it been loaded as text?

These posts may help. 

Why don’t my dates work?

Get the Dates Right

Highlighted
Creator
Creator

Re: max date

THanks colin for sharing.

have seen this. But I have followed the same format

Highlighted
Master III
Master III

Re: max date

Try

=timestamp(timestamp#(Mid('XYZ_20_11_2016_00_12',Index('XYZ_20_11_2016_00_12','_')+1),'DD_MM_YYYY_hh_mm'),'MM/DD/YYYY hh:mm:ss TT')

and  use FileBaseName()  (it purge .csv)  instead of FileName()

Highlighted

Re: max date

The right expression is this

=TimeStamp(TimeStamp#(Left(Right('XYZ_20_11_2016_00_12.csv',20),16),'DD_MM_YYYY_hh_mm'),'MM/DD/YYYY hh:mm:ss TT')

M is for Month

m is for Minute

Highlighted

Re: max date

Use the function  filetime('filename') to get the file time not timestamp.

File functions ‒ QlikView

Highlighted
Creator
Creator

Re: max date

actually it was file time earlier, but filetime is different from the time that is there in Name.Hence has to go by this

Highlighted
Creator
Creator

Re: max date

Hi Sunny,

I'm able to convert to time stamp. Problem here is, when use max  of the expression its not returning anything

Highlighted

Re: max date

Can you show few screenshots of the issue?

Highlighted

Re: max date

What does num(your_timestamp) return?

Is the timestamp value in a data table or in a variable?


Can you display max(your_timestamp) in a test chart?

I find charts are the best way to evaluate and test expressions as you do not need to reload the data each time. Then once the expression is correct, you can move the expression into the load script.

Can you show your actual script / expressions?

Partner
Partner

Re: max date

Hi

my two cents would be to replace _ by - for date and - by : for time like


Step 1

Keepchar('XYZ_20_11_2016_00_12.csv', ‘0123456789’)   as str will keep 201120160012

Step 2

Mid(str,5,4) & ‘-‘ & mid(str,3,2) & ‘-‘ Left(str,2) & ’T‘ & mid(str,9,2) & ‘:’ & right(str,2)  as str2

will format as 2016-11-20T00:12           

Step 3

TimeStamp(    TimeStamp#(str2 ,'DD_MM_YYYY_hh_mm'), 'MM/DD/YYYY hh:mm:ss TT') will format as timestamp

You can LOAD like

LOAD
*, TimeStamp( TimeStamp#(str2    ,'DD_MM_YYYY_hh_mm'), 'MM/DD/YYYY hh:mm:ss TT') as Date

;
LOAD
*,  Mid(str,5,4) & ‘-‘ & mid(str,3,2) & ‘-‘ Left(str,2) & ’T‘ & mid(str,9,2) & ‘:’ & right(str,2) as str2

;
LOAD
*,
Keepchar('XYZ_20_11_2016_00_12.csv', ‘0123456789’)   as str

From Table;

Drop fields str1, str2;

best regards

juju