topic Re: Conditional concatenate in QlikView

Conditional concatenate

Anonymous — Wed, 26 Aug 2015 14:56:48 GMT

Hi,

I remember I saw a QV file with a conditional concatenate once, but I cannot find it can I cn't remember how it was used it was for a loop to caoncatenate all tabs in an excel file, it was something like the below code, but I can't make it work, anyone knows how to do it?

(some excel tabs have some extra columns that's why I need to use concatenate)

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

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

if $(i)>0 then

$(=Concatenate);

ENDIF

Weeks:

LOAD *

FROM $(FN) (ooxml, embedded labels, table is [$(sheetName)]);

Thanks!

Re: Conditional concatenate

stigchel — Wed, 26 Aug 2015 15:11:01 GMT

This what I once found:

//Load all sheets in an excel file

DIRECTORY;

Table:

load '' as field autogenerate 0;

For Each vFile in FileList('*.xlsx')

trace file=$(vFile);

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

Sheets:

SQLTABLES;

DISCONNECT;

For i = 0 To NoOfRows('Sheets')-1

Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);

trace $(vSheet);

concatenate(Table)

LOAD '$(vSheet)' as [Tab Name],

From [$(vFile)]

(ooxml, embedded labels, table is [$(vSheet)]);

Next;

NEXT;

Re: Conditional concatenate

m_woolf — Wed, 26 Aug 2015 15:12:28 GMT

I searched the forum:

Load multiple sheets and excel files

Re: Conditional concatenate

Anonymous — Wed, 26 Aug 2015 15:21:40 GMT

Thanks Piet, this did the trick

Table:

load '' as field autogenerate 0;

concatenate(Table)