topic Loading Multiple Excel Sheets Script. in QlikView

Loading Multiple Excel Sheets Script.

Anonymous — Fri, 03 Jul 2015 12:08:49 GMT

hi,

Beginner in qlik view.

I used the below script from QV community to load multiple excel worksheets and it worked.All the worksheets have the same format.

When i tried to create a list box for Month i got numbers such 41060, 41323 instead of JAN and Feb.

Q1) Where should i declare the Monthname function or any other functions/expression within this //script shown below so that it is applicable to all the worksheets?

I tried to declare it after the LOAD* statement, but QV threw up an error.

Please advice

//Script

ODBC CONNECT32 TO [Excel Files;DBQ=C:\Users\..\Documents\qlik_documents\qlik view training\yy\Work_Data_1.xlsx];

LET vfile='C:\Users\.....\Documents\.....\qlik view training\yy\Work_Data_1.xlsx';

MM_IPC_TABLES:

SQLTABLES;

DISCONNECT;

FOR i=0 to NoOfRows('MM_IPC_TABLES')-1

Let vExcelsheets=purgechar(purgechar(Peek('TABLE_NAME',i,'MM_IPC_TABLES'),Chr(36)),chr(39));

LOAD*,

'$(vExcelsheets)' AS Sheet

From

$(vfile)(ooxml,embedded labels,header is 1 lines, Table is [$(vExcelsheets)]);

Re: Loading Multiple Excel Sheets Script.

swuehl — Fri, 03 Jul 2015 12:13:32 GMT

Instead of using the star symbol, use the embedded labels of your excel files in your LOAD statement.

Assuming a field named Month, it will look like

FOR i=0 to NoOfRows('MM_IPC_TABLES')-1

Let vExcelsheets=purgechar(purgechar(Peek('TABLE_NAME',i,'MM_IPC_TABLES'),Chr(36)),chr(39));

LOAD Month,

'$(vExcelsheets)' AS Sheet

From

$(vfile)(ooxml,embedded labels,header is 1 lines, Table is [$(vExcelsheets)]);

NEXT

Your Excel file seems to store the Month as a date value, so if you want to have a Month or Monthname, use QV date functions to transform / format:

FOR i=0 to NoOfRows('MM_IPC_TABLES')-1

Let vExcelsheets=purgechar(purgechar(Peek('TABLE_NAME',i,'MM_IPC_TABLES'),Chr(36)),chr(39));

LOAD Month as MonthNum,

Month(Month) as Month,

MonthName(Month) as MonthName,

'$(vExcelsheets)' AS Sheet

From

$(vfile)(ooxml,embedded labels,header is 1 lines, Table is [$(vExcelsheets)]);

NEXT

Re: Loading Multiple Excel Sheets Script.

jonathandienst — Fri, 03 Jul 2015 12:17:15 GMT

Presumably one of the fields you are loading with LOAD * is a date field or a month field (you've left us guessing a bit there). You will need to explicitly load this field (or all the fields), At least, something like this (Change this to the actual field names in your model):

LOAD *,

Date(Date) As DateF,

Month(Date) As Month,

Year(Date) As Year,

....

Now use DateF.

You can't say

LOAD *,

Date(Date) As Date,

...

because the date field already exists in *.