Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
atsushi_saijo
Creator II
Creator 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
swuehl
MVP
MVP

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;

View solution in original post

10 Replies
swuehl
MVP
MVP

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

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

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;

View solution in original post

atsushi_saijo
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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

atsushi_saijo
Creator II
Creator II
Author

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

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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!