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

Create Empty Table for a Looping Load

It is common to load from a set to files using a loop like a ForEach over a FileList. If the files are not identical, you need to explicitly concatenate them, but you cannot use concatenate in the first iteration of the loop as the table does not exist yet. One common solution is this:

Set vTable = ‘MyTable:’;

For ......

  $(vTable)

  LOAD ……

  ;

  Set vTable = ‘Concatenate(MyTable)’;

Next

and clean up the variable at the end of the load:

Set vTable = ;

There is another way that I prefer. Create an empty table before the loop. Like this:

MyTable:

LOAD 0 as ID AutoGenerate 0;

For ......

  Concatenate(MyTable))

  LOAD ……

  ;

Next


(Use a field that is in the main load inside the loop - ID in this example).

AutoGenerate 0 creates an empty table. The other columns are added as required by the loads in the loop. There is no clean up necessary and the syntax checker is not confused by the embedded variable.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
3 Replies

Re: Create Empty Table for a Looping Load

Thanks for sharing this Jonathan‌. I have been using this a lot lately whereas earlier I used to be scared using loops in these situations when I did not know about creating a table using AutoGenerate 0. I actually picked this technique from some of the responses from jagan‌.

And just to add this also works for Joins aswell, the only thing is that within the loop you need to have a field on which you can join on, else join would work just as concatenate.

Best,

Sunny

Re: Create Empty Table for a Looping Load

Yeah, good trick. I reckon a lot of experienced users use this. But it's a good idea to share such techniques with the community. Keep 'm coming.


talk is cheap, supply exceeds demand

Re: Create Empty Table for a Looping Load

I have been using the same in many applications. Some times I also used inline load like below

Table:

LOAD * inline [

Junk ];

Community Browser