Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
Has your date / timestamp data been loaded in the correct format or has it been loaded as text?
These posts may help.
THanks colin for sharing.
have seen this. But I have followed the same format
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()
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
Use the function filetime('filename') to get the file time not timestamp.
actually it was file time earlier, but filetime is different from the time that is there in Name.Hence has to go by this
Hi Sunny,
I'm able to convert to time stamp. Problem here is, when use max of the expression its not returning anything
Can you show few screenshots of the issue?
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?
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