topic Re: How to use "drop table"? in QlikView

How to use "drop table"?

vchuprina — Thu, 23 Jul 2015 20:33:27 GMT

Hi guys,

I receive files with the same data, but with different structure and for this reason I use sheetname to determine type of file.

Script works correct, but it dublicate data. I think it's happening because I don't drop Temp_Tables after first "IF", but when do this I can't use Temp_Tables for the second "if".

Please advise some way. What I should change?

ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)];

// Read list of sheets

Temp_Tables:

sqltables;

DISCONNECT;

// Get just the file name

let vFileName = mid(vFile, index(vFile, '\', -1) + 1);

// Enumerate sheets

for iSheet = 0 to NoOfRows('Temp_Tables') - 1

let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables');

let vSheetName = replace(replace(replace(vSheetName, chr(39), ''), chr(36),''),'#','.');

IF(WildMatch((vSheetName),'*DETAIL*') AND NOT WildMatch((vSheetName),'*Print*','*Database*')) THEN

ELSE

IF (WildMatch((vSheetName),'*PREFALL*', 'PRE-FALL')) Then

END IF

DROP TABLE Temp_Tables;

Re: How to use "drop table"?

sunny_talwar — Thu, 23 Jul 2015 20:52:03 GMT

May be this:

ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)];

// Read list of sheets

Temp_Tables:

sqltables;

Temp1:

LOAD Concat(DISTINCT TABLE_NAME, '|') as Concat

Count(DISTINCT TABLE_NAME, '|') as Count

Resident Temp_Tables;

LET vConcat = Chr(39) & Peek('Concat') & Chr(39);

LET vCount = Peek('Count');

DROP Tables Temp1, Temp_Tables;

DISCONNECT;

// Get just the file name

let vFileName = mid(vFile, index(vFile, '\', -1) + 1);

// Enumerate sheets

for iSheet = 1 to $(vCount)

let vSheetName = SubField($(vConcat), '|', $(iSheet));

let vSheetName = replace(replace(replace(vSheetName, chr(39), ''), chr(36),''),'#','.');

Re: How to use "drop table"?

Qrishna — Thu, 23 Jul 2015 20:59:18 GMT

try as below

CONNECT TO [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=workbook.xls;Extended Properties="Excel 8.0;"];

tables:

SQLtables;

DISCONNECT; // Don't need ODBC connection anymore

One of the fields loaded by SQLtables is "TABLE_NAME". This field will contain the sheet name.

We will loop through this set of sheet names.

FOR i = 0 to NoOfRows('tables')-1

Assign the TABLE_NAME to the variable "sheetName".

TABLE_NAMEs that contain spaces will be enclosed in single quotes.

The purgeChar function will remove any quotes.

LET sheetName = purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39));

// Optional filtering logic to select certain sheets

IF wildmatch('$(sheetName)', 'Sales*') THEN // Only sheetNames that begin "Sales"

Sales:

// Now that we have a sheet name, a standard biff (Excel) load can be used.

LOAD *,

'$(sheetName)' as Sheet // Optionally, the sheetName value may be loaded as a field

FROM workbook.xls (biff, embedded labels, table is [$(sheetName)]);

END IF // End of optional sheet filtering

DROP TABLE tables; // The table list is no longer needed

Re: How to use "drop table"?

vchuprina — Thu, 23 Jul 2015 21:12:18 GMT

I try use your script, but received following issue.

Could you please look at this

Error in expression:

Count takes 1 parameter

Temp1:

LOAD Concat(DISTINCT TABLE_NAME, '|') as Concat,

Count(DISTINCT TABLE_NAME, '|') as Count

Resident Temp_Tables

Re: How to use "drop table"?

sunny_talwar — Thu, 23 Jul 2015 21:23:04 GMT

my bad

Temp1:

LOAD Concat(DISTINCT TABLE_NAME, '|') as Concat,

Count(DISTINCT TABLE_NAME) as Count

Re: How to use "drop table"?

vchuprina — Thu, 23 Jul 2015 21:43:27 GMT

Sunny thank you for your help, now it works, but shows following issue. It seems that subfield functions doesn't work

Script line error:

Re: How to use "drop table"?

vchuprina — Fri, 24 Jul 2015 11:42:27 GMT

What is the reason of issue? Maybe qlikview has limited string length

Re: How to use "drop table"?

sunny_talwar — Fri, 24 Jul 2015 11:46:44 GMT

Actually I need to think about this a little

Re: How to use "drop table"?

vchuprina — Mon, 03 Aug 2015 17:22:49 GMT

Sunny, you still haven't any ideas?