topic Re: In context of For Each ... FileList(), how to make NoConcatenate in QlikView

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

atsushi_saijo — Thu, 16 Jun 2016 10:39:12 GMT

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?

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

swuehl — Thu, 16 Jun 2016 10:45:24 GMT

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

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

trdandamudi — Thu, 16 Jun 2016 10:46:35 GMT

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

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

swuehl — Thu, 16 Jun 2016 10:47:11 GMT

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;

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

atsushi_saijo — Thu, 16 Jun 2016 10:56:04 GMT

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.

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

atsushi_saijo — Thu, 16 Jun 2016 10:56:36 GMT

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

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

atsushi_saijo — Thu, 16 Jun 2016 11:01:10 GMT

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

Test: $(vConcat) LOAD *

$(vConcat) Test: LOAD *

Well thanks for feedback!

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

trdandamudi — Fri, 17 Jun 2016 11:13:54 GMT

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...

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

atsushi_saijo — Fri, 17 Jun 2016 15:45:09 GMT

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!

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

atsushi_saijo — Fri, 17 Jun 2016 15:46:43 GMT

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!

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

trdandamudi — Fri, 17 Jun 2016 15:59:54 GMT

No issues... great you are able to resolve your issue..

You have a nice weekend...