loop inside load statement

[Peony]

Hi all.

Is there any possibility to use loop or something like it inside load statement?

I have an excel table which is edited manually by users.  From this table, I always need to load the first 4 columns with names like A, B, C, D and all columns that ended with the symbol "_GRE". Since users can delete some columns with a symbol "_GRE"  or add new ones, I can’t simply list them in the LOAD. I think such a load can be implemented in a loop, but I don’t understand how to do it. Can anyone help me with solution?

[tresesco]

You can try like:

//Load all fields using '*' with single record

temp:

Load * From <> where recno()=1;

//Then find the first four fields and put them in variables

Let vField1='['&FieldName(1, temp)&']';
Let vField2='['&FieldName(2, temp)&']';
Let vField3='['&FieldName(3, temp)&']';
Let vField4='['&FieldName(4, temp)&']';

//Load all records for these fields

t1:

Load
    $(vField1),
    $(vField2),
    $(vField3),
    $(vField4)

From <>;

Drop table temp;

[Peony]

Hi @tresesco 

Thank you for your idea. Unfortunately this method can't help me to load with first for columns all columns that ended with the symbol "_GRE".

[tresesco · Accepted solution]

In that case, try like:

temp:

Load * From <> where recno()=1;

let j=0;
For i=1 to NoOfFields('temp')

   if $(j)<5 and WildMatch(FieldName($(i), 'temp'), '*_GRE') then
     j=j+1;
     let vField$(j)='['&FieldName($(i), 'temp')&']';
   end if
Next

t1:
Load
   $(vField1),
   $(vField2),
   $(vField3),
   $(vField4)
From <>;

Drop table temp;

[Peony]

@tresesco  Thank you much for your idea!