topic Re: [_xlnm#_FilterDatabase] --- urgent help please in QlikView

[_xlnm#_FilterDatabase] --- urgent help please

Thu, 18 Jun 2015 13:12:33 GMT

Hi,

I have excel sheet from Jan to Dec in excel workbook. I used script to load all excel sheets at a time in qvw. when reloading it is showing below error.

Field not found - <Name?

(ID)>

[_xlnm#_FilterDatabase]:

can anyone suggest me this how to sort our this. I google the error but not found solution.

Thanks.

Re: [_xlnm#_FilterDatabase] --- urgent help please

sunny_talwar — Thu, 18 Jun 2015 13:14:20 GMT

It seems that one of the tabs (assuming the months are in different tabs) doesn't have Name field in it which might be causing this issue. Can you post the script?

Best,

Sunny

Re: [_xlnm#_FilterDatabase] --- urgent help please

avinashelite — Thu, 18 Jun 2015 13:17:43 GMT

Hi Amelia,

I think the field count are not consistent across all the files i.e few files have column by name eg A and some file does not have ...because of which its causing the issue.

Check for the particular filed

Re: [_xlnm#_FilterDatabase] --- urgent help please

giakoum — Thu, 18 Jun 2015 13:21:51 GMT

a sample excel file would help

remove rows but not the columns so that we can check

Re: [_xlnm#_FilterDatabase] --- urgent help please

Thu, 18 Jun 2015 13:39:08 GMT

Thanks.

here is the script

tables:
SQLTables;

DISCONNECT;

Tables1:
load
*,
replace(TABLE_NAME,chr(39),'') as NEW
resident tables;

DROP Table tables;

let var=NoOfRows('Tables1');

FOR i = 0 to $(var)-1
let sheetName=subfield(peek('NEW', i,'Tables1'),'$',1);
// exit script;
[$(sheetName)]:

Data:
LOAD [Name?
(ID)],
     Team,
     [Date Taken],
     [number],
     [Correct],
     Comments

FROM
[..\..\Folders\Test.xlsx]
(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

DROP Table Tables1;

//exit script;

STORE Fact into C:\Qlikview\Test.qvd (qvd);
DROP Table Data;

Re: [_xlnm#_FilterDatabase] --- urgent help please

kuba_michalik — Thu, 18 Jun 2015 13:41:48 GMT

I guess you are getting the sheet names using the SQLTables statement? If so, and you have filters enabled in some sheets of the Excel, they create extra tables/sheets (not visible when opening Excel file normally) where the filter state is stored.

Either get rid of all autofilters in the source Excel, or add a check and don't try to load anything from tables/sheets that have "FilterDatabase" in the name.

Re: [_xlnm#_FilterDatabase] --- urgent help please

sunny_talwar — Thu, 18 Jun 2015 13:43:44 GMT

Data:
LOAD [Name?
(ID)],
    Team,
    [Date Taken],
    [number],
    [Correct],
    Comments

FROM
[..\..\Folders\Test.xlsx]
(ooxml, embedded labels, table is [$(sheetName)]);

Is the field name called [Name? Make sure you are using the correct field name. And also you are missing a comma (,) after the fieldname [Name?

Make those changes and see if it runs without error.

Best,

Sunny

Re: [_xlnm#_FilterDatabase] --- urgent help please

kuba_michalik — Thu, 18 Jun 2015 13:45:24 GMT

Change the appropriate part of your script to this (add a wrapping IF to check table name - and I hope you don't have any actual sheets named like "xlnm#_FilterDatabase" 😞

FOR i = 0 to $(var)-1
let sheetName=subfield(peek('NEW', i,'Tables1'),'$',1);

IF Index('$(sheetName)','FilterDatabase')=0 then
// exit script;
[$(sheetName)]:

Data:
LOAD [Name?
(ID)],
     Team,
     [Date Taken],
     [number],
     [Correct],
     Comments

FROM
[..\..\Folders\Test.xlsx]
(ooxml, embedded labels, table is [$(sheetName)]);

ENDIF

NEXT i

Re: [_xlnm#_FilterDatabase] --- urgent help please

Thu, 18 Jun 2015 13:46:35 GMT

actually the field name in excel is [Name?(ID)]

would it not work if the field name have braces?

Re: [_xlnm#_FilterDatabase] --- urgent help please

sunny_talwar — Thu, 18 Jun 2015 13:48:19 GMT

Can you share the excel file with just the header row and no other data???

Re: [_xlnm#_FilterDatabase] --- urgent help please

Thu, 18 Jun 2015 13:55:18 GMT

I observed this when loading table files

Re: [_xlnm#_FilterDatabase] --- urgent help please

giakoum — Thu, 18 Jun 2015 13:57:25 GMT

try it with brackets. and make sure it is continuous on the same line

Re: [_xlnm#_FilterDatabase] --- urgent help please

martinpohl — Thu, 18 Jun 2015 13:57:25 GMT

within the sqltables statement you get sheets but also areas, defined in your Excel-file.

In that case the sheet name is the same with the area-name, prefixed with an $.

so load only table, ending with an $ in your loop.

Regards

Re: [_xlnm#_FilterDatabase] --- urgent help please

settu_periasamy — Thu, 18 Jun 2015 14:10:11 GMT

Hi,

may be the hidden objects in excel (not hidden sheet).

For checking purpose, copy 3 excel sheets, paste special value into another workbook.. then try with the same script

Re: [_xlnm#_FilterDatabase] --- urgent help please

Thu, 18 Jun 2015 14:21:30 GMT

Thanks. where I need to use $. please let me know

Re: [_xlnm#_FilterDatabase] --- urgent help please

martinpohl — Thu, 18 Jun 2015 14:26:04 GMT

in your script here:

Tables1:

load

replace(TABLE_NAME,chr(39),'') as NEW

resident tables;

change to

Tables1:

load

replace(TABLE_NAME,chr(39),'') as NEW

resident tables

where TABLE_NAME like '*$'

;

but I'm not sure if a $ works in a statement.

Otherwise replace it in load load script in a new field

e.g. replace(TABLE_NAME,'$','TAB') as TABLE_NAME_NEW

and use

where TABLE_NAME like '*TAB'

Re: [_xlnm#_FilterDatabase] --- urgent help please

Thu, 18 Jun 2015 14:38:16 GMT

Thanks. it's working.

Re: [_xlnm#_FilterDatabase] --- urgent help please

Thu, 18 Jun 2015 14:40:53 GMT

Thanks to all!

Re: [_xlnm#_FilterDatabase] --- urgent help please

Anonymous — Tue, 11 Aug 2015 08:15:54 GMT

Thanks Jakub...it is working fine for me...given code remove unwanted files from the excels or csv and provide you required file only.

Re: [_xlnm#_FilterDatabase] --- urgent help please

pgalvezt — Wed, 08 Feb 2017 14:57:41 GMT

If you use Store Sentence. You have to use too the Jakub's script : Because store Save a qvd( "xlnm#_FilterDatabase")..

IF Index('$(sheetName)','FilterDatabase')=0 then

STORE Table1 into $(LocationQvd)NameQVD $(sheetName).qvd;

DROP TABLE Table1;

ENDIF

Thank you Jakub your solution is nice