
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Excel sources - Added new column in Excel
Hi All,
My dashboard data source is Excel files. The names of those excel are like Alpha012018, Alpha032019, Alpha052019
Alpha012018, Alpha032019 had columns A, B ,C and dashboard was working fine. Now I have a request to add one more column in Alpha052019 like A, B ,C, D and load the data for all the files. (Additional column to excel)
Alpha012018 A, B ,C
Alpha013019 A, B ,C
Alpha052019 A, B ,C, D
------------------------------------------------------
I tried logic as
Table1:
Load A,B,C, '' as D
from Alpha*
Where (FileName) <052019 /////// created date out of file name and wrote the filter
//////// loading data from new file with additional column
Table2:
Load A,B,C, D
from Alpha*
Where (FileName) >= 052019 /////// created date out of file name and wrote the filter
------------------------------------------------------
Error: Now here,
Table 2 is trying to look column D into files 022018, 032019 since I am writing Alpha* while ignoring my where condition.
------------------------------------------------------
I am ready to rewrite the code if someone provide me the logic to make my requirement work.
- Tags:
- qlikview_scripting

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello Sakura!
I tried following code in the script.
Works fine.
for each file in FileList('yourPath') //Your path where the excel file is stored example 'D:\test\test\test.xlsx' ODBC CONNECT32 TO [Excel Files;DBQ=$(file)]; tables: SQLtables; DISCONNECT; FOR i = 0 to NoOfRows('tables')-1 LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)); Table_with_data: LOAD *, '$(sheetName)' as Sheet // Optionally, the sheetName value may be loaded as a field FROM $(file) (ooxml, embedded labels, table is [$(sheetName)]); NEXT;
I tried it with the excel example and .qvw file attached below.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
and my qvw
