Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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 *.