script expression to pull time out of this file name?

[stevelord]

Hi, I have some left(right() functions pulling dates out of this filename format without issue, but am having trouble hitting the trick for the time hh:mm part of it.

File name is like this:

abc123_11-May-2014_00-48.xlsx

where the 00-48 is 12:48 am from a machine stamping this with 24-hour timestamps.  I don't really care how the time is formatted, I just need to distinguish the abc123_...00-48 file from the ...19-50 file that came through on the same day for some reason.  (We should only be getting one file per day, but for some reason an extra one shows up, and they conflict and null each other if on the same day and data in other fields overlap.)

offhand, left(right(Filename(),10),2) should get the hours, and 7),2) should get the minutes, but concatenating around a &':'& isn't working and various other approaches resist.

Simplest expression I've tried that's not working

     NUM(left(Right(Filename(),10),2))&':'&NUM(left(Right(Filename(),7),2)) as FileTime,

I get no error messages until a load from the qvd this is stored in stops and says field not found - FileTime.

Any help is appreciated, thanks!

[swuehl · Accepted solution]

You are building a string, on which you can't apply a number format function num().

Try a time interpretation function:

time#(left(Right(Filename(),10),2))&':'&NUM(left(Right(Filename(),7),2),'hh:mm') as FileTime,

[eduardo_sommer]

Just as a second possibility:

=Time(MakeTime(subfield(subfield(subfield(Filename(), '.',1),'_',4),'-',1),subfield(subfield(subfield(FileName(), '.',1),'_',4),'-',2)),'hh:mm')

Eduardo

[felipe_dutra]

And a third:

Replace(SubField(FileBaseName(), '_', 3), '-', ':') as FileTime

[stevelord]

Correct answer to Swuehl for first and easiest to translate from what I had.

Real issue turned out to be that I had added this field to two of the six crosstable loads self-concatenating into a qvd, and the qvd would only pick up the first one and three other tables that didn't have the field.  (qlikview only automatically concatenates tables with identical lists of fields being loaded)  Since I can't do a concatenate load on a crosstable load, I stuck the time string into the other four crosstable loads for fun and the issue resolved.

Solutions provided here will help me get my time field properly formatted for correct handling now too though.

[stevelord]

Oh yeah, other funny thing is I was adding the time field to try to help separate files because a particular data point wasn't coming through on a graph.  Issue there was that I had the label in the expression box instead of the expression in the expression box.  Qlikcommunity feel free to dock me 5 points for that one.