Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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:
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!
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
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
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