Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
vchuprina
New Contributor II

How to use "drop table"?

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

END IF

NEXT

DROP TABLE Temp_Tables;

8 Replies

Re: How to use "drop table"?

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),''),'#','.');

krishna_2644
Valued Contributor III

Re: How to use "drop table"?

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

NEXT

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

vchuprina
New Contributor II

Re: How to use "drop table"?

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"?

my bad

Temp1:

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

      Count(DISTINCT TABLE_NAME) as Count

vchuprina
New Contributor II

Re: How to use "drop table"?

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

Script line error:

let vSheetName = SubField(''BA Detail$'FilterDatabase|'BA Detail$'Print_Titles|'BA Summary$'Print_Titles|'F16 Detail$'|'F16 Detail$'FilterDatabase|'F16 Detail$'Print_Titles|'F16 Summary$'|'F16 Summary$'Print_Titles|'Prior Detail$'FilterDatabase|'Prior Detail$'Print_Titles|'SPIN Data$'FilterDatabase|'Total Recap$'FilterDatabase|'Total Recap$'Print_Area|'Total Recap$'Print_Titles', '|', 1)

vchuprina
New Contributor II

Re: How to use "drop table"?

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

Re: How to use "drop table"?

Actually I need to think about this a little

vchuprina
New Contributor II

Re: How to use "drop table"?

Sunny, you still haven't any ideas?

Community Browser