Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

toddbuss
New Contributor III

Data Load trick: Insert date from filename into field

On my Qlik Sense data load, I'm able to import several excel documents, but I need to insert a field on every record that holds a date-stamp that is only available in the file name.  The code below inserts nulls into the ReportDate fields.  I realize i'll need to massage the formatting of the field so that it's a date, but I'd like to succeed at importing the value as text before I clean it up.  Am I on the right track?

[Bed Survey]:

LOAD

    @1 as Region,

    @2 as [Facility Code],

    @3 as [Facility Name],

    @4 as [Bed Number],

    ...(etc)...

    AutoNumber(RowNo(),[@4]) as [Line Number],

  right('$(file)',11) as ReportDate

   

FROM [lib://Conn_HealthServicesData (central_office_oconrkt)/Inpatient Beds\*.XLS]

(txt, codepage is 1252, no labels, delimiter is '\t', msq, header is 1 lines);

Thanks,

Todd

1 Solution

Accepted Solutions
MVP
MVP

Re: Data Load trick: Insert date from filename into field

May be try this

Right(FileBaseName(), 10) as ReportDate

3 Replies
MVP
MVP

Re: Data Load trick: Insert date from filename into field

May be try this

Right(FileBaseName(), 10) as ReportDate

toddbuss
New Contributor III

Re: Data Load trick: Insert date from filename into field

Thanks!  That was simple enough.  Do you know where I might find a list of pre-defined variables like FileBaseName?  For example, Is there a system variable for the file's save-dates? (from windows).

MVP
MVP

Re: Data Load trick: Insert date from filename into field

Check here for some file related functions

File functions ‒ QlikView

For save date, may be this

FileTime ‒ QlikView

Community Browser