Loading Multiple Excel Sheets Script.

[Anonymous]

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)]);

NEXT

[swuehl · Accepted solution]

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

[jonathandienst]

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 *.