In context of For Each ... FileList(), how to make NoConcatenate

[atsushi_saijo]

Hi Gurus,

In the context of For Each vFileLlist FileList (..path..) .... Next, how could I make the loaded table NoConcatenate?

I must be able to load first loop as NoConcatenate, and second onwards as Concatenate (..first table..).

Example:

For Each vFileList FileList (C:\Test\*xlsx)

Test: LOAD *

from vFileList (xlsx.....)

Next vFileList

*Please assume: the excel files in C:\Test\*xlsx has 90% common field names.

So this will create Test table. However, if we have to refactor long ETL process created by other people, and need to add single table during ETL process only (not used for end users), and if we have had some common field name inadvertently, then QlikView will concatenate automatically to the previous table.

Would you possibly advise if there are any way to force NoConcatenat-ed table in this context?

[swuehl]

You can do it like

Let vPrefix = '';

For Each vFileList FileList (C:\Test\*xlsx)

Test:

$(vPrefix)

LOAD *

from vFileList (xlsx.....)

Let vPrefix = 'NOCONCATENATE';

Next vFileList

Let vPrefix = NULL();

edit: Variable should be set to 'CONCATENATE' of course

[trdandamudi]

May be using a variable as below:

LET vConcat = 'NOCONCATENATE''

$(vConcat)

For Each vFileList FileList (C:\Test\*xlsx)

Test: LOAD *

from vFileList (xlsx.....)

vConcat ='CONCATENATE''

Next vFileList

[swuehl · Accepted solution]

Another option, something like

Test:

LOAD 0 as Dummy

Autogenerate 0;

For Each vFileList FileList (C:\Test\*xlsx)

CONCATENATE (Test)

LOAD *

from vFileList (xlsx.....)

Next vFileList

DROP FIELD Dummy;

[atsushi_saijo]

Many thanks for the fast reponse. Perhaps I will let vPrefix as NoConcatenate, then duting first loop, make it as Concatenate()? I am testing the code now. 

[atsushi_saijo]

And thanks, this indeed is quite simple. I might like it.

[atsushi_saijo]

Many thanks for fast feedback. The position of $(vConcat)  is just before the LOAD syntax?

Test: $(vConcat) LOAD *

or

$(vConcat)  Test: LOAD *

Well thanks for feedback!

[trdandamudi]

It will work either way:

Example1:

SET vconcat = 'Chr(39) &NoConcatenate Chr(39)';

Table1:

LOAD * Inline [

ID,Amount 

10,1000

20,2000

];

$(=vconcat)

Table2:

Load * inline [

test

abc

];

Example2:

SET vconcat = 'Chr(39) &NoConcatenate Chr(39)';

Table1:

LOAD * Inline [

ID,Amount 

10,1000

20,2000

];

Table2:

$(=vconcat)

Load * inline [

test

abc

];

Hope this helps...

[atsushi_saijo]

Hi, I selected this as answer, although 2 developers have kindly advised solution. Why this is better? Because syntax check will not put it as error. The use of $(v....) value makes syntax check error, although it is not error when it is parsed. Thanks for all the care!

[atsushi_saijo]

Many thanks for the reply. Yes, I wondered, when I was refactoring, some dev write Tbl: NoConcatenate LOAD .... others NoConcatenate Tbl: LOAD. I prefer latter because I notice immediately that I did NoC. I selected other solution but I appreciate for your fast contribution!