Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Sept. 16, Qlik Product Portfolio Strategy and Roadmap for Data Integration 11 AM ET REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
carlcimino
Creator II
Creator II

Load same sheet from multiple files

Hello, I have a directory with multiple files with the same name prefix.  FieldGlass_History.xlsx, FieldGlass_0819.xlsx, FieldGlass_0719.xlsx, etc.  Each contains 1 sheet with the same name.  I tried the script below but it didn't work.  How would I go about writing one load statement to iterate through the files?  Any help would be much appreciated.

for each vFileName in Filelist ('\\nausp-wfil0001\ApplicationOwnerFiles\Global_Finance\North America Expenses\North_America_Expenses_Includes\FieldGlass*.xlsx')


FACT_FIELDGLASS:
LOAD
"Invoice ID" as INVOICE_ID
, Supplier as VENDOR_NAME
, 'Job Posting by '&"Job Posting Owner"&' for a '&"Job Posting Title"&' '&"Labor Type"&' employee to be located at '&Site as DESCR
, date(floor("Invoice Submit Date")) as INVOICE_DATE
, num#(Date(MonthStart("Invoice Approved Date"), 'YYYYMM')) as DIM_ACTG_YR_MO_SK
, Currency as BASE_CURRENCY
, "Invoice Amount" as POSTED_TOTAL_AMT
, "Account Code Code" as DEPT_CD
, BUGL as BU_CD
, if(len(trim("GL Code (Professional)"))=0,"GL Code (Admin/Clerical)","GL Code (Professional)") as ACCOUNT_CD
, OU as OPER_UNIT_CD
, 'FIELDGLASS' as EXP_SOURCE
, 'PRE' as ALLOCATION_TYPE
, (filetime()) as SOURCE_DATE
FROM $(vFileName)
(ooxml, embedded labels, header is 1 lines, table is [Spend_Report_-_AGS_Metrics])
;

next vFileName

store FACT_FIELDGLASS into 'lib://NA_Exp_BIFileshare_QVD/FACT_FIELDGLASS.qvd' (qvd);

drop table FACT_FIELDGLASS;
 
Here's the error I get.
Error.jpg
Labels (4)
1 Solution

Accepted Solutions
treysmithdev
Luminary Alumni
Luminary Alumni

I think this should work. One thing is, if you aren't running in Legacy Mode, your FileList path parameter needs to be off a Lib connection.

FACT_FIELDGLASS:
Load
    Timestamp(Null()) as SOURCE_DATE
AutoGenerate
    (0);

// Is Legacy Mode On? If not, FileList path should be a a lib connection.
for each vFileName in Filelist ('\\nausp-wfil0001\ApplicationOwnerFiles\Global_Finance\North America Expenses\North_America_Expenses_Includes\FieldGlass*.xlsx')


Concatenate(FACT_FIELDGLASS)
LOAD
"Invoice ID" as INVOICE_ID
, Supplier as VENDOR_NAME
, 'Job Posting by '&"Job Posting Owner"&' for a '&"Job Posting Title"&' '&"Labor Type"&' employee to be located at '&Site as DESCR
, date(floor("Invoice Submit Date")) as INVOICE_DATE
, num#(Date(MonthStart("Invoice Approved Date"), 'YYYYMM')) as DIM_ACTG_YR_MO_SK
, Currency as BASE_CURRENCY
, "Invoice Amount" as POSTED_TOTAL_AMT
, "Account Code Code" as DEPT_CD
, BUGL as BU_CD
, if(len(trim("GL Code (Professional)"))=0,"GL Code (Admin/Clerical)","GL Code (Professional)") as ACCOUNT_CD
, OU as OPER_UNIT_CD
, 'FIELDGLASS' as EXP_SOURCE
, 'PRE' as ALLOCATION_TYPE
, (filetime()) as SOURCE_DATE
FROM [$(vFileName)] // Needs brackets since there is a space in the path
(ooxml, embedded labels, header is 1 lines, table is [Spend_Report_-_AGS_Metrics])
;

next vFileName

store FACT_FIELDGLASS into 'lib://NA_Exp_BIFileshare_QVD/FACT_FIELDGLASS.qvd' (qvd);

drop table FACT_FIELDGLASS;
Blog: WhereClause   Twitter: @treysmithdev

View solution in original post

3 Replies
treysmithdev
Luminary Alumni
Luminary Alumni

I think this should work. One thing is, if you aren't running in Legacy Mode, your FileList path parameter needs to be off a Lib connection.

FACT_FIELDGLASS:
Load
    Timestamp(Null()) as SOURCE_DATE
AutoGenerate
    (0);

// Is Legacy Mode On? If not, FileList path should be a a lib connection.
for each vFileName in Filelist ('\\nausp-wfil0001\ApplicationOwnerFiles\Global_Finance\North America Expenses\North_America_Expenses_Includes\FieldGlass*.xlsx')


Concatenate(FACT_FIELDGLASS)
LOAD
"Invoice ID" as INVOICE_ID
, Supplier as VENDOR_NAME
, 'Job Posting by '&"Job Posting Owner"&' for a '&"Job Posting Title"&' '&"Labor Type"&' employee to be located at '&Site as DESCR
, date(floor("Invoice Submit Date")) as INVOICE_DATE
, num#(Date(MonthStart("Invoice Approved Date"), 'YYYYMM')) as DIM_ACTG_YR_MO_SK
, Currency as BASE_CURRENCY
, "Invoice Amount" as POSTED_TOTAL_AMT
, "Account Code Code" as DEPT_CD
, BUGL as BU_CD
, if(len(trim("GL Code (Professional)"))=0,"GL Code (Admin/Clerical)","GL Code (Professional)") as ACCOUNT_CD
, OU as OPER_UNIT_CD
, 'FIELDGLASS' as EXP_SOURCE
, 'PRE' as ALLOCATION_TYPE
, (filetime()) as SOURCE_DATE
FROM [$(vFileName)] // Needs brackets since there is a space in the path
(ooxml, embedded labels, header is 1 lines, table is [Spend_Report_-_AGS_Metrics])
;

next vFileName

store FACT_FIELDGLASS into 'lib://NA_Exp_BIFileshare_QVD/FACT_FIELDGLASS.qvd' (qvd);

drop table FACT_FIELDGLASS;
Blog: WhereClause   Twitter: @treysmithdev

View solution in original post

trdandamudi
Master II
Master II

It looks like it is not bringing in any records into the table. Before using the "Store" command, do you have any records in "FACT_FIELDGLASS" table ?

 

carlcimino
Creator II
Creator II
Author

Ah that was it.  I replaced the path with the Lib connection.  I also didn't realize that the space in the path would require the From vFileName to need brackets.