3 Replies Latest reply: Jun 7, 2017 2:55 PM by Sunny Talwar RSS

    Data Load trick:  Insert date from filename into field

    Todd Buss

      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