Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create date field using file names in incremental load

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!

1 Solution

Accepted Solutions
maxgro
MVP
MVP

maybe

Let Path_Hospira='yourpathhere\Hospira*.xlsx';

For each file in FileList('$(Path_Hospira)')

  trace $(file);

    

  // file name without suffix

  LET f = replace(subfield('$(file)', '\', -1), '.xlsx', '');

  trace $(f);

  // year, month, month num

  LET Year = Right('$(f)', 4);

  LET Month = (Mid('$(f)', 9, 3));

  // your MonthNames var should match the Month var 

  LET MonthNum = Num(Div(Index(Lower('$(MonthNames)'), Lower('$(Month)')),4)+1, '00');

  trace $(Year);

  trace $(Month) - $(MonthNum);

 

  Hospira_raw:

  LOAD

     ...............

NEXT

View solution in original post

2 Replies
maxgro
MVP
MVP

maybe

Let Path_Hospira='yourpathhere\Hospira*.xlsx';

For each file in FileList('$(Path_Hospira)')

  trace $(file);

    

  // file name without suffix

  LET f = replace(subfield('$(file)', '\', -1), '.xlsx', '');

  trace $(f);

  // year, month, month num

  LET Year = Right('$(f)', 4);

  LET Month = (Mid('$(f)', 9, 3));

  // your MonthNames var should match the Month var 

  LET MonthNum = Num(Div(Index(Lower('$(MonthNames)'), Lower('$(Month)')),4)+1, '00');

  trace $(Year);

  trace $(Month) - $(MonthNum);

 

  Hospira_raw:

  LOAD

     ...............

NEXT

Not applicable
Author

Got it! The problem is on the file name. The trace function is so useful to save time on debugging.

Thank you so much for helping me identify the problem. Massimo