Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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 - Creator II
Partner - Creator II

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