Create Empty Table for a Looping Load

[jonathandienst]

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.

[sunny_talwar]

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

[Gysbert_Wassenaar]

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.

[Kushal_Chawda]

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

Table:

LOAD * inline [

Junk ];