topic Excel sources - Added new column in Excel in QlikView

Excel sources - Added new column in Excel

Sakura — Sat, 16 Nov 2024 21:06:43 GMT

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.

Re: Excel sources - Added new column in Excel

gf — Tue, 09 Apr 2019 06:03:52 GMT

Do you want to append each sheet to display the result in, for example, one straight table?

Re: Excel sources - Added new column in Excel

gf — Tue, 09 Apr 2019 06:25:11 GMT

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.

Re: Excel sources - Added new column in Excel

gf — Tue, 09 Apr 2019 06:26:39 GMT

and my qvw