Qlik Community

Qlik Sense App Development

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

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Luminary
Luminary

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
Highlighted
Luminary
Luminary

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

Highlighted
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 ?

 

Highlighted
Creator II
Creator II

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.