topic Create Empty Table for a Looping Load in QlikView

Create Empty Table for a Looping Load

jonathandienst — Fri, 06 May 2016 07:57:14 GMT

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)’;

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

;

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

Re: Create Empty Table for a Looping Load

sunny_talwar — Fri, 06 May 2016 08:48:28 GMT

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

Gysbert_Wassenaar — Fri, 06 May 2016 08:53:07 GMT

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.

Re: Create Empty Table for a Looping Load

Kushal_Chawda — Fri, 06 May 2016 09:03:43 GMT

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

Table:

LOAD * inline [

Junk ];