Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

stevelord
Not applicable

script expression to pull time out of this file name?

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!

1 Solution

Accepted Solutions
swuehl
Not applicable

Re: script expression to pull time out of this file name?

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,

5 Replies
swuehl
Not applicable

Re: script expression to pull time out of this file name?

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
Not applicable

Re: script expression to pull time out of this file name?

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
Not applicable

Re: script expression to pull time out of this file name?

And a third:

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

stevelord
Not applicable

Re: script expression to pull time out of this file name?

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
Not applicable

Re: script expression to pull time out of this file name?

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.