topic Re: Loop sheet load error in QlikView

Loop sheet load error

rcorcoran — Mon, 28 May 2018 11:08:21 GMT

Hi I am getting the error below when I try to load an excel sheet. this worked previously but has stopped working now for 2018 excel document though it will still load one with the same code for 2017 code I am using is below the error. Would appreciate any help

Unknown file format specifier:

table is 'Acute KPI$'FilterDatabase

Targets_temp2018:

CrossTable(TargetReferredMM, Target, 😎

LOAD @1 AS TargetCode,

@2 AS [Metric Description],

@3 AS [Metric Shortened Title],

@4 AS [Reporting Frequency],

@5,

@6 AS Number,

@7 AS [Cumulative Or PIT],

@8 AS AnnualTarget,

@9 AS 1,

@10 AS 2,

@11 AS 3,

@12 AS 4,

@13 AS 5,

@14 AS 6,

@15 AS 7,

@16 AS 8,

@17 AS 9,

@18 AS 10,

@19 AS 11,

@20 AS 12

FROM

[Acute Targets 2018.xls]

(biff, no labels, header is 1 lines, table is 'Acute KPI$'FilterDatabase, filters(

Remove(Col, Pos(Top, 37)),

Remove(Col, Pos(Top, 36)),

Remove(Col, Pos(Top, 23)),

Remove(Col, Pos(Top, 22)),

Script

AcuteSheets_temp:

//AcuteSheets:

SQLTABLES;

DISCONNECT;

//exit script;

AcuteSheets:

NOCONCATENATE LOAD *

RESIDENT AcuteSheets_temp

// WHERE TABLE_TYPE='SYSTEM TABLE'

WHERE match(TABLE_NAME,'Acute KPI$')=0

;

DROP TABLE AcuteSheets_temp;

//EXIT SCRIPT;

FOR i = 0 to NoOfRows('AcuteSheets')-1 //Loop and set number of tables

//Get name of each sheet

LET sheetName = peek('TABLE_NAME', i, 'AcuteSheets');

//LET sheetName = purgechar(PurgeChar(peek('TABLE_NAME', i, 'AcuteSheets'), chr(39)),'$');

TRACE Loading data from $(sheetName);

Targets_temp2018:

CrossTable(TargetReferredMM, Target, 😎

LOAD @1 AS TargetCode,

@2 AS [Metric Description],

@3 AS [Metric Shortened Title],

@4 AS [Reporting Frequency],

@5,

@6 AS Number,

@7 AS [Cumulative Or PIT],

@8 AS AnnualTarget,

@9 AS 1,

@10 AS 2,

@11 AS 3,

@12 AS 4,

@13 AS 5,

@14 AS 6,

@15 AS 7,

@16 AS 8,

@17 AS 9,

@18 AS 10,

@19 AS 11,

@20 AS 12

FROM

[Acute Targets 2018.xls]

(biff, no labels, header is 1 lines, table is $(sheetName), filters(

Remove(Col, Pos(Top, 37)),

Remove(Col, Pos(Top, 36)),

Remove(Col, Pos(Top, 23)),

Remove(Col, Pos(Top, 22)),

Remove(Col, Pos(Top, 21)),

Remove(Col, Pos(Top, 20)),

Remove(Col, Pos(Top, 19)),

Remove(Col, Pos(Top, 17)),

Remove(Col, Pos(Top, 16)),

Remove(Col, Pos(Top, 15)),

Remove(Col, Pos(Top, 13)),

Remove(Col, Pos(Top, 10)),

Remove(Col, Pos(Top, 8)),

Remove(Col, Pos(Top, 7)),

Remove(Col, Pos(Top, 6)),

Remove(Col, Pos(Top, 5)),

Remove(Col, Pos(Top, 2)),

Replace(1, top, StrCnd(null)),

Replace(2, top, StrCnd(null)),

Replace(3, top, StrCnd(null)),

Replace(4, top, StrCnd(null)),

Replace(7, top, StrCnd(null))

//Remove(Row, RowCnd(CellValue, 5, StrCnd(equal, 'National Total'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(equal, 'National'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(Contain, 'Group'))),

//Remove(Row, RowCnd(CellValue, 5, StrCnd(Contain, 'Hospitals Group')))

));

Re: Loop sheet load error

marcus_sommer — Mon, 28 May 2018 11:19:40 GMT

Maybe your 2018 file isn't a xls else a xlsx and then the fileformat changed from biff to ooxml.

- Marcus

Re: Loop sheet load error

rcorcoran — Mon, 28 May 2018 11:34:05 GMT

Thanks Marcus but I checked and they are all .xls

Re: Loop sheet load error

marcus_sommer — Mon, 28 May 2018 11:47:40 GMT

The bold part looked wrong:

(biff, no labels, header is 1 lines, table is 'Acute KPI$'FilterDatabase, filters(

probably some copy+paste error.

- Marcus

Re: Loop sheet load error

rcorcoran — Mon, 28 May 2018 12:01:56 GMT

Its something to do with the excel document a when I rename the 2017 to 2018 and use that it loads.

Re: Loop sheet load error

rcorcoran — Mon, 28 May 2018 12:05:04 GMT

if the tabs on the 2018 excel document were re-ordered would this cause the error?

Re: Loop sheet load error

marcus_sommer — Mon, 28 May 2018 12:25:28 GMT

The order of the sheets or of the fieldnames within in it aren't important but the correct spelling of the sheet- and fieldnames are.

I think the easiest way to find the correct spelling for the names and the load-syntax would be to use the load table-wizard - just the whole sheet without the renaming stuff, filters and other transformations and then you could compare the fileformat and sheetname and so on and if you find a difference just use copy & paste to correct your previous load-statement (then commenting the new one and if everything worked you could delete it - if not you need to repeat it again because there must be other issues, too).

- Marcus

Re: Loop sheet load error

Peter_Cammaert — Mon, 28 May 2018 12:35:50 GMT

As far as I can see, the following is not a correct table specification in the LOAD statement. IMHO the reload engine tries to interprete everything after the seond quote as a format specifier. Which it clearly isn't.

... table is 'Acute KPI$'FilterDatabase...

Now, your sheet names are read from a SQL database. Are you sure their names are correct in your DB?

Re: Loop sheet load error

rcorcoran — Tue, 05 Jun 2018 09:52:13 GMT

Hi All

I know the reason for the error in the loop sheets has something to do with the Acute KPI and the filters in it - it seems to be adding in a table Filter Database as a copy of Acute KPI however I cannot seem to stop this even when I turn off the filters. I was wondering if you had advice for the code below on how I can put in for it to ignore table Filter Database??

LET sheetName = peek('TABLE_NAME', i, 'AcuteSheets');

//LET sheetName = purgechar(PurgeChar(peek('TABLE_NAME', i, 'AcuteSheets'), chr(39)),'$');

TRACE Loading data from $(sheetName);

Re: Loop sheet load error

marcus_sommer — Tue, 05 Jun 2018 11:02:37 GMT

Did you create (like above suggested) a new load-statement with file-wizard (without the filter-stuff) and after them comparing this load-statement with your origin (looping) ones? You should see then some differences.

- Marcus