topic Re: script error in QlikView

script error

Tue, 16 Feb 2016 13:15:12 GMT

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;

Re: script error

jerem1234 — Tue, 16 Feb 2016 20:53:02 GMT

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!!

Re: script error

settu_periasamy — Wed, 17 Feb 2016 05:35:18 GMT

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;

Re: script error

jagan — Wed, 17 Feb 2016 08:44:09 GMT

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.

Re: script error

Wed, 17 Feb 2016 12:32:14 GMT

Thanks Jeremiah! This is what I need!