Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

script error

Can you help me with the below script? I tried to run it with multiple spreadsheets, each spreadsheet would contain similar data with Supplier (Vendor) and Type in the first 2 columns but with different date for different FY.

The script runs .... but I get the below script error.

Thank you

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/MM/YYYY';
SET TimestampFormat='D/MM/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

directory;

FOR EACH vFile in FileList('D:\Users\d642527\Desktop\Example\*.xlsx');

ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];
Sheets:
SQLTABLES;
DISCONNECT;
For i = 0 To NoOfRows('Sheets')-1

Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);

//================================================== 
  FY14:
CrossTable(Date, Data, 3)
LOAD
'$(vSheet)'
as [Tab Name],
Supplier,
Type,
[41821],
[41852]

FROM
[$(vFile)]
(
ooxml, embedded labels, table is $(vSheet))
WHERE(not IsNull(Supplier));
Next;
DROP Table Sheets;
Next;




Final:
LOAD  [Tab Name],
Supplier,
Type as [Project Type],
Date(Num#(Date)) AS Reporting_Date,
Date(Floor(Num#(Date)),'MMM-YY') as MonthYear,
Data
Resident FY14;
Drop Table FY14;


//==========================================================

FY15:
CrossTable(Date, Data, 3)
LOAD
'$(vSheet)'
as [Tab Name],
Supplier,
Type,
[42186],
[42217]
FROM
[$(vFile)]
(
ooxml, embedded labels, table is $(vSheet))
WHERE(not IsNull(Supplier));
Next;
DROP Table Sheets;
Next;

Final:
LOAD  [Tab Name],
Supplier,
Type as [Project Type],
Date(Num#(Date)) AS Reporting_Date,
Date(Floor(Num#(Date)),'MMM-YY') as MonthYear,
Data

Resident FY15;
Drop Table FY15;

1 Solution

Accepted Solutions
jerem1234
Specialist II
Specialist II

So your problem is that you don't want to hardcode the Date Fields (ex: 41821 and 41852) since they may vary for each file? Have you tried adding a '*' in your load to load in all fields?

For example:

CrossTable(Date, Data, 3)
LOAD
'$(vSheet)'
as [Tab Name],
*
FROM
[$(vFile)]
(
ooxml, embedded labels, table is $(vSheet))
WHERE(not IsNull(Supplier));


Hope this helps!!

View solution in original post

4 Replies
jerem1234
Specialist II
Specialist II

So your problem is that you don't want to hardcode the Date Fields (ex: 41821 and 41852) since they may vary for each file? Have you tried adding a '*' in your load to load in all fields?

For example:

CrossTable(Date, Data, 3)
LOAD
'$(vSheet)'
as [Tab Name],
*
FROM
[$(vFile)]
(
ooxml, embedded labels, table is $(vSheet))
WHERE(not IsNull(Supplier));


Hope this helps!!

View solution in original post

settu_periasamy

Hi,

Based on your Previous thread sample data Re: How to load EXCEL worksheets and retrieve tab names

You can try the below script for Multiple sheet with Multiple Headers

Updated:

DIRECTORY;

For Each vFile in FileList('FY1314_Report.xlsx')

     ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

     Sheets:

     SQLTABLES;

     DISCONNECT;

      For i = 0 To NoOfRows('Sheets')-1

          Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1); 

   Temp:

  CrossTable(Date, Data, 3)

  LOAD '$(vSheet)' as [Tab Name],*

  FROM

  [$(vFile)]

  (ooxml, embedded labels, table is $(vSheet)) Where not IsNull(VENDOR);

    Next;

     DROP Table Sheets;

Next;

Final:

LOAD [Tab Name],

  VENDOR,

  TYPE as [Project Type],

  Date(Num#(Date)) AS Reporting_Date,

  Date(Floor(Num#(Date)),'MMM-YY') as MonthYear,

  Data

Resident Temp;

Drop Table Temp;

jagan
MVP
MVP

Hi,

Try like this, instead of columns just put *

CrossTable(Date, Data, 3)
LOAD
'$(vSheet)'
as [Tab Name],
*

FROM
[$(vFile)]
(
ooxml, embedded labels, table is $(vSheet))
WHERE(not IsNull(Supplier));
Next;
DROP Table Sheets;
Next;


Regards,

Jagan.

Not applicable
Author

Thanks Jeremiah! This is what I need!