topic Re: Create date field using file names in incremental load in QlikView

Create date field using file names in incremental load

Fri, 07 Apr 2017 18:26:52 GMT

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:

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;

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:

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!

Re: Create date field using file names in incremental load

maxgro — Fri, 07 Apr 2017 19:30:01 GMT

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

Re: Create date field using file names in incremental load

Tue, 11 Apr 2017 16:01:03 GMT

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