Do while with variable

[kris_vliegen]

Hello all,

I'm trying to load data from an Iseries.

And every bookyear is in another Company and also an other filepath(accesspath)

I have an excel with Year and CompanyNumber.

Boekjaar,     BoekLib

2011     11E

2012     12E

2013     13E

2014     14E

I have only need to refresh the last 2 years (current year and CurrentYear -1)

Now I need to do a select like this

select *

FROM S0678ADT.X62FIL13EO.PBP;

and

Select *

FROM S0678ADT.X62FIL14EO.PBP;

Here is an example of my script where my BoekLib (in Yellow) is empty...

How can I fill up "BoekLib"?

Regards,

Kris

[marcus_sommer]

You used for your load within the loop the functionality that qv automatically concatenated tables which have the same fields. Is there any difference between the tables qv create a new table with a number-suffix.

In this case you should check your data and if they are correct you need a forced concatenate:

for vFileNo ...

     let vConcatenate = if($(vFileNo) = 1, '', 'concatenate(MyTable)');

     ....

     MyTable:

     $(vConcatenate)

     Load ....

     SQL ....

next

- Marcus

[kris_vliegen]

Marcus,

thanks for you respond.

As I go and check the files there is indeed a difference between the files.

I  had also found a solution that was working.

Lib:

LOAD

     Boekjaar,

     BoekLib

FROM

[..\01 Table Files\Boekhoudjaren.xlsx]

(ooxml, embedded labels, table is Blad1) ;

PBPTemp:

load * inline [

Temp

1

];

For vFileNo = 1 to NoOfRows('Lib')

      LET TMP = Peek('BoekLib',$(vFileNo)-1,'Lib');

      LET vYear = Peek('Boekjaar',$(vFileNo)-1,'Lib');

      Let vFileName = 'S0678ADT.X62FIL' & '$(TMP)' & 'O.PBP';

      Concatenate(PBPTemp)

      PBPTemp:

      Load *,

       '$(vYear)' as Year;

      SQL SELECT *

FROM $(vFileName) ;

   Next vFileNo

Drop Table Lib;

NoConcatenate

PBP:

LOAD

     BPKODE,

     BPINTR,

     BPHOVE,

     BPFILL,

     Year

Resident PBPTemp where Temp <> 1 ;

Drop Table PBPTemp ;