Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vchuprina
New Contributor II

Subfield issue

I try to use following script, but it return issue. Subfield function doesn't work, it seems that string is too long. Did somebody had similar problem. Please advise how you resolve it

++++++++++++++++++++++++++++++++++++++++

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

++++++++++++++++++++++++++++++++++++++++++++++++++++++++

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)

Tags (1)
4 Replies
mwoolf
Honored Contributor II

Re: Subfield issue

The apostrophes are problematic.

MVP
MVP

Re: Subfield issue

I think it's not the length of the string, but the quoting.

Have you considered instead of using concat() / subfield() to just peek() the TABLE_NAME from Temp_Tables in your loop?

vchuprina
New Contributor II

Re: Subfield issue

I've added replace function, but it still not works

Replace(Concat(DISTINCT TABLE_NAME, '|'),chr(39),'') as Concat

vchuprina
New Contributor II

Re: Subfield issue

I tried do this, but had the same result

Community Browser