2 Replies Latest reply: Apr 11, 2017 12:01 PM by yuan du RSS

    Create date field using file names in incremental load

    yuan du

      Hi Qlikview Gurus,

       

      Objective:

      I'm trying to use this code to load several excel files in the same folder. I only need 2 values from each excel. and then create the date fields from the file names.

       

      Here are my files in the folder:

      Fields.PNG

      Below is my code:

      Let Path_Hospira='---\\Auto Programming\Hospira for Peds\Hospira*.xlsx';

       

      For each file in FileList(Path_Hospira)

       

      Let Year = Right('$(File)',4);

      Let Month = Mid('$(File)',9,3);

       

      Hospira_raw:

      LOAD

           F11 as AUTOPROGRAMMED,

           F33 as FIN,

           'CHILD' as FACILITY,

           '$(Year)' as year,

           '$(Month)' as Month,

           '$(Year)' & Num('$(Month)','00') as YearMonth

      FROM

      $(file)

      (ooxml, embedded labels, table is [Page 1]) where RecNo() = 25;

      NEXT

       

      Store Hospira_raw into \\Auto programming\QVDs\Hospira_raw.qvd (qvd);

       

      Problem:

      The problem is that my date fields don't return the value. See below:

      tables.PNG

       

      Could you help with the problem? Please let me know if you have any question.

      I attached the raw data and my QVW here for you to play with.

      Thank you in advance!