Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
atsushi_saijo
Contributor II

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

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?

1 Solution

Accepted Solutions
MVP
MVP

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

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;

10 Replies
MVP
MVP

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

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
Honored Contributor

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

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






MVP
MVP

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

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
Contributor II

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

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
Contributor II

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

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

atsushi_saijo
Contributor II

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

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
Honored Contributor

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

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
Contributor II

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

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
Contributor II

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

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!

Community Browser