Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
kmswetha
Creator
Creator

max date

Hi,

I'm having a file name where I'm extracting date along with time stamp, using timestamp(timestamp(filename)). So far is good.

How ever when I say max  of that field, it returns' -.'

Not sure whats happening.

Any suggestions pls?

25 Replies
colin_albert

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

kmswetha
Creator
Creator
Author

THanks colin for sharing.

have seen this. But I have followed the same format

antoniotiman
Master III
Master III

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

sunny_talwar

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

colin_albert

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

File functions ‒ QlikView

kmswetha
Creator
Creator
Author

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

kmswetha
Creator
Creator
Author

Hi Sunny,

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

sunny_talwar

Can you show few screenshots of the issue?

colin_albert

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?

jujucts
Partner
Partner

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