Subfield issue

vchuprina — Fri, 24 Jul 2015 11:46:28 GMT

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:

Re: Subfield issue

m_woolf — Fri, 24 Jul 2015 12:00:52 GMT

The apostrophes are problematic.

Re: Subfield issue

swuehl — Fri, 24 Jul 2015 12:05:03 GMT

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?

Re: Subfield issue

vchuprina — Fri, 24 Jul 2015 14:14:19 GMT

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

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

Re: Subfield issue

vchuprina — Fri, 24 Jul 2015 14:17:31 GMT

I tried do this, but had the same result

topic Re: Subfield issue in QlikView

Subfield issue

Re: Subfield issue

Re: Subfield issue

Re: Subfield issue

Re: Subfield issue